필요한 자료가 복수개의 테이블에 분산되어 저장되어 있으며 공통의 컬럼으로 관계를 형성하고 있을 때
이 관계를 이용하여 자료를 추출하는 연산이 JOIN이다.
구분
- 내부조인(INNER JOIN) / 외부조인(OUTER JOIN)
- 일반조인 / ANSI JOIN
- 그 밖에 CARTESIAN JOIN (CROSS JOIN), NATURAL JOIN
내부조인
- 조인조건에 일치하는 자료만으로 결과를 도출
- 조인조건을 만족하지 않는 자료는 무시함
- 동등조인(EQUI-JOIN), 비동등조인(NONE EQUI-JOIN), INNER JOIN(ANSI JOIN)
일반조인 사용형식
SELECT [테이블별칭.] 컬럼명 [AS 별칭] [,]
:
[테이블 별칭.] 컬럼명 [AS 별칭]
FROM 테이블명 [별칭], 테이블명 [별칭] [, 테이블명 [별칭] ... ]
WHERE 조인조건
[AND 일반조건]
:
- 테이블명 [별칭] : 사용되는 모든 테이블의 컬럼명이 모두 다른 경우 '별칭'은 생략 가능
- '테이블 별칭'은 SELECT 절이나 WHERE 절 등에서 이름이 동일한 컬럼명들을 참조할때는 반드시 사용해야 함
- 조인조건 : 사용되는 테이블 사이의 공통 컬럼을 동등연산자('=')으로 사용한 조건식이나 (EQUI-JOIN), 동등연산자('=') 이외의 연산자를 사용한 조건식(NONE EQUI-JOIN)을 기술
- 조인조건과 일반조건은 AND 연산자로 연결함
ANSI 조인 사용형식
SELECT [테이블별칭.] 컬럼명 [AS 별칭] [,]
:
[테이블 별칭.] 컬럼명 [AS 별칭]
FROM 테이블명 [별칭]
INNER JOIN 테이블명 [별칭] ON ( 조인조건 [ AND 일반조건] )
INNER JOIN 테이블명 [별칭] ON ( 조인조건 [ AND 일반조건] )
:
[WHERE 일반조건]
:
첫번째 JOIN되는 테이블은 반드시 직접조인이 되어야함
첫번째 INNER JOIN된 테이블(FROM + INNER JOIN 1)을 2번째 JOIN을 해준다.
1) CARTESIAN JOIN (CROSS JOIN)
(ANSI FORMAT)
SELECT column_list
FROM table_name
CROSS JOIN table_name [ON join_condition] ;
- 조인조건이 생략되었거나 잘못된 조인조건이 부여된 경우 결과는 두 테이블의 행은 곱한 갯수와 열은 더한 결과를 반환
- 반드시 필요한 경우가 아니면 수행 자제
SELECT COUNT(*)
FROM BUYPROD, PROD, CART;
SELECT COUNT(*)
FROM BUYPROD
CROSS JOIN PROD
CROSS JOIN CART;
둘 다 같은 결과가 출력
결과는 2267064
각각의 카운트 값을 곱한 값과 동일 (148 * 74 * 207)
BUYPROD와 PROD CROSS JOIN후 그 조인한 결과를 CART와 또 CROSS JOIN
2) 동등조인(Equi join)
- 조인조건에 동등연산자 '='이 사용된 조인
- 대부분의 조인이 동등조인임
사원테이블에서 근속년수가 5년 이상인 사원들의 사원번호, 사원명, 부서명, 입사일을
조회하시오
일반조인 형태
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_NAME AS 부서명,
HIRE_DATE AS 입사일
FROM HR.EMPLOYEES, HR.DEPARTMENTS
WHERE HR.EMPLOYEES.DEPARTMENT_ID = HR.DEPARTMENTS.DEPARTMENT_ID
--별칭을 붙이징낳는다면 풀네임을 붙여야됨 이름이 같아서 꼭 지정해야함
AND EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) >=5;
ANSI JOIN
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_NAME AS 부서명,
HIRE_DATE AS 입사일
FROM HR.EMPLOYEES A
INNER JOIN HR.DEPARTMENTS B ON(A.DEPARTMENT_ID = B.DEPARTMENT_ID)
WHERE EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) >=5;
--INNER JOIN HR.DEPARTMENTS B ON(A.DEPARTMENT_ID = B.DEPARTMENT_ID AND
-- EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) >=5);
2020년 1~3월 제품별 매입집계를 조회하시오 Alias는 제품코드, 제품명, 매입수량, 매입금액
+100개 이상의 매입수량만 조회
일반조인
SELECT A.PROD_ID AS 제품코드,
A.PROD_NAME AS 제품명,
SUM(B.BUY_QTY) AS 매입수량,
SUM(BUY_QTY*PROD_COST) AS 매입금액
FROM PROD A, BUYPROD B
WHERE B.BUY_DATE BETWEEN TO_DATE('20200101') AND LAST_DAY(TO_DATE('20200301'))
AND A.PROD_ID=B.BUY_PROD
GROUP BY A.PROD_ID,A.PROD_NAME,B.BUY_QTY
--HAVING SUM(B.BUY_QTY)>=100
ORDER BY 1;
ANSI JOIN
SELECT A.PROD_ID AS 제품코드,
A.PROD_NAME AS 제품명,
SUM(B.BUY_QTY) AS 매입수량,
SUM(BUY_QTY*PROD_COST) AS 매입금액
FROM PROD A
INNER JOIN BUYPROD B ON(A.PROD_ID=B.BUY_PROD AND B.BUY_DATE
BETWEEN TO_DATE('20200101') AND LAST_DAY(TO_DATE('20200301')))
GROUP BY A.PROD_ID,A.PROD_NAME,B.BUY_QTY
ORDER BY 1;
2020년 1~6월 상품의 분류별 판매집계를 조회하시오 Alias는 분류코드, 분류명, 판매금액
일반조인
SELECT L.LPROD_GU AS 분류코드,
L.LPROD_NM AS 분류명,
SUM(P.PROD_PRICE * C.CART_QTY) AS 판매금액
FROM LPROD L, PROD P, CART C
WHERE P.PROD_ID=C.CART_PROD
AND SUBSTR(C.CART_NO,1,6) BETWEEN '202001' AND '202006'
AND L.LPROD_GU=P.PROD_LGU
GROUP BY L.LPROD_GU, L.LPROD_NM;
ANSI JOIN
SELECT L.LPROD_GU AS 분류코드,
L.LPROD_NM AS 분류명,
SUM(P.PROD_PRICE * C.CART_QTY) AS 판매금액
FROM LPROD L
INNER JOIN PROD P ON (L.LPROD_GU=P.PROD_LGU)
INNER JOIN CART C ON (P.PROD_ID=C.CART_PROD AND
SUBSTR(C.CART_NO,1,6) BETWEEN '202001' AND '202006')
GROUP BY L.LPROD_GU, L.LPROD_NM;
HR계정에서 부서가 미국 이외에 위치한 부서에 근무하는 사원정보를 조회하시오
Alias는 사원번호, 사원명, 부서코드, 부서명
미국의 국가코드는 'US'임
일반조인
SELECT A.EMPLOYEE_ID AS 사원번호,
A.EMP_NAME AS 사원명,
A.DEPARTMENT_ID AS 부서코드,
B.DEPARTMENT_NAME AS 부서명
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B, HR.LOCATIONS C
WHERE C.COUNTRY_ID != 'US'
AND B.LOCATION_ID=C.LOCATION_ID
AND A.DEPARTMENT_ID=B.DEPARTMENT_ID;
ANSI JOIN
SELECT A.EMPLOYEE_ID AS 사원번호,
A.EMP_NAME AS 사원명,
A.DEPARTMENT_ID AS 부서코드,
B.DEPARTMENT_NAME AS 부서명
FROM HR.EMPLOYEES A
INNER JOIN HR.DEPARTMENTS B ON (A.DEPARTMENT_ID=B.DEPARTMENT_ID)
INNER JOIN HR.LOCATIONS C ON(B.LOCATION_ID=C.LOCATION_ID
AND C.COUNTRY_ID != 'US');
외부조인
- 내부조인은 조인조건을 만족하지 않는 자료를 무시한(자료의 종류가 적은 쪽을 기준)
결과를 반환 - 외부조인은 자료의 종류가 많은 쪽을 기준으로 적은 쪽에 NULL행을 추가하여 조인을 수행함
- 일반 외부 조인 경우
- 조인조건 기술시 자료의 종류가 적은 쪽에 외부조인 연산자 '(+)'를 기술
- 조인조건이 여러개 이고 모두 외부 조인이 필요한 경우 해당되는 모든 조건에 '(+)'를
사용해야 함
- 한 테이블이 동시에 여러번 외부조인에 사용될 수 없다 예를 들어 3 테이블
A, B, C가 외부조인되는 경우 A를 기준으로 B가 외부조인되고, 동시에 C를 기준으로
B가 외부조인 될 수 없다(A=B(+) AND C=B(+)는 허용안됨)
- 일반 외부조인에서 일반 조건이 부여된다면 결과는 내부조인 결과가 반환됨
=>해결책으로 ANSI외부조인이나 서브쿼리를 사용해야 한다
일반 외부조인
SELECT 컬럼list, ...
FROM 테이블명1 [별칭1], 테이블명2 [별칭2] [, 테이블명3 [별칭3], ... ]
WHERE [별칭1.]컬럼명=[별칭2.]컬럼명(+)
[ AND [별칭1.]컬럼명=[별칭2.]컬럼명(+)
:
ANSI외부조인
SELECT 컬럼list, ...
FROM 테이블명1 [별칭1]
LEFT | RIGHT | FULL OUTER JOIN 테이블명2 [별칭2] ON (조인조건 [AND 일반조건])
:
[WHERE 일반조건]
- LEFT OUTER JOIN : 테이블명1의 자료의 종류가 테이블명2 자료의 종류보다 많을 때
- RIGHT OUTER JOIN : 테이블명1의 자료의 종류가 테이블명2 자료의 종류보다 적을 때
- FULL OUTER JOIN : 양쪽 테이블 자료의 종류가 각각 적을 때
- WHERE 일반조건 : 모든 테이블에 공통으로 적용되는 조건을 기술해야 함 이 절을 사용하면 결과가 내부조인 결과가 반환됨
모든 분류별 상품의 수를 조회하시오 Alias는 분류코드, 분류명, 상품의수
더보기
SELECT DISTINCT PROD_LGU
FROM PROD; -- 6개
SELECT LPROD_GU
FROM LPROD; -- 9개
일반 외부조인
SELECT L.LPROD_GU AS 분류코드, --많은쪽을 넣어야함 적은쪽을 넣으면 NULL출력
L.LPROD_NM AS 분류명,
COUNT(P.PROD_LGU) AS "상품의 수" --외부조인에선 COUNT(*) 쓰면안됨 NULL도 하나의 데이터로 측정
FROM LPROD L, PROD P
WHERE L.LPROD_GU=P.PROD_LGU(+) --적은쪽에 (+)
GROUP BY L.LPROD_GU, L.LPROD_NM;
ANSI JOIN
SELECT L.LPROD_GU AS 분류코드,
L.LPROD_NM AS 분류명,
COUNT(P.PROD_LGU) AS "상품의 수"
FROM LPROD L
LEFT OUTER JOIN PROD P ON(L.LPROD_GU=P.PROD_LGU)
GROUP BY L.LPROD_GU, L.LPROD_NM
ORDER BY 1;
2020년 7월 모든 회원별 구매금액집계 (회원번호, 회원명, 구매금액합계)를 조회하시오.
일반 외부조인
SELECT M.MEM_ID AS 회원번호,
M.MEM_NAME AS 회원명,
SUM(C.CART_QTY*P.PROD_PRICE) AS 구매금액합계
FROM MEMBER M, CART C, PROD P
WHERE M.MEM_ID=C.CART_MEMBER (+)
--AND P.PROD_ID=C.CART_PROD 모든 회원별이기 때문에 상품별로 늘릴 필요는 없다
AND SUBSTR(CART_NO,1,6)='202007'
GROUP BY M.MEM_ID,M.MEM_NAME;
ANSI JOIN
SELECT M.MEM_ID AS 회원번호,
M.MEM_NAME AS 회원명,
NVL(SUM(C.CART_QTY*P.PROD_PRICE),'0') AS 구매금액합계
FROM MEMBER M
LEFT OUTER JOIN CART C ON (M.MEM_ID=C.CART_MEMBER)
LEFT OUTER JOIN PROD P ON (P.PROD_ID=C.CART_PROD AND SUBSTR(CART_NO,1,6)='202007')
GROUP BY M.MEM_ID,M.MEM_NAME
ORDER BY 1;
서브 쿼리
더보기
2020년 7월 회원별 구매금액집계
SELECT A.CART_MEMBER AS CID,
SUM(A.CART_QTY*B.PROD_PRICE) AS CSUM
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE '202007%'
GROUP BY A.CART_MEMBER;
SELECT M.MEM_ID AS 회원번호,
M.MEM_NAME AS 회원명,
NVL(C.CSUM,0) AS 구매금액합계
FROM MEMBER M, (SELECT A.CART_MEMBER AS CID,
SUM(A.CART_QTY*B.PROD_PRICE) AS CSUM
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE '202007%'
GROUP BY A.CART_MEMBER) C
WHERE M.MEM_ID=C.CID (+)
ORDER BY 1;
'DB Oracle' 카테고리의 다른 글
[Oracle] DML명령 (서브쿼리 이용) (0) | 2024.03.26 |
---|---|
[Oracle] 서브쿼리 (0) | 2024.03.25 |
[Oracle] ★ 주요 문 형식 (0) | 2024.03.19 |
[Oracle] 함수 (0) | 2024.03.13 |
[Oracle] '||' , 표현식CASE (0) | 2024.03.13 |