OUTER-JOIN
- OUTER-JOIN 이해하기
아우터-조인(OUTER-JOIN)은 조인 조건에 만족하지 않은 데이터도 결과에 나오도록 하는 조인이다.
아우터-조인에는 '기준 데이터 집합'과 '참조 데이터 집합'이 있다.
- 기준 데이터 집합 : 아우터-조인의 기준이 되는 집합('아우터-집합' 이라고도 한다.)
- 참조 데이터 집합 : 아우터-조인의 참조가 되는 집합
'기준 데이터 집합'은 조인 조건을 만족하지 않아도 모두 결과에 포함된다. 단, 필터 조건은 만족해야 한다.
아우터-조인을 사용하려면, 조인 조건 컬럼 한쪽에 '(+)' 표시를 추가하면 된다. 조인 조건에 '(+)'가 있으면 아우터-조인, 없으면 이너-조인이다.
'기준 데이터 집합'과 '참조 데이터 집합'은 '(+)' 표시로 구분한다. 조인 조건에 '(+)' 표시가 붙은 쪽은 '참조 데이터 집합', '(+)' 표시가 없는 쪽은 '기준 데이터 집합'이다.
예제 ) 이너-조인과 아우터-조인의 비교
-- CUS_ID가 'CUS_0002'의 조건을 갖는 M_CUS와 T_ITM_EVL 조인
SELECT CUS_ID ,CUS_NM
FROM M_CUS
WHERE CUS_ID = 'CUS_0002';
SELECT CUS_ID ,ITM_ID ,EVL_LST_NO
FROM T_ITM_EVL
WHERE CUS_ID = 'CUS_0002';
고객(M_CUS)에서 'CUS_0002'의 데이터가 존재하지만 아이템평가(T_ITM_EVL)에는 'CUS_0002'의 데이터가 없다.
이를 가지고 이너-조인 및 아우터-조인을 하면 결과는 아래와 같이 나온다.
-- 이너-조인
SELECT T1.CUS_ID ,T1.CUS_NM ,T2.CUS_ID ,T2.ITM_ID ,T2.EVL_LST_NO
FROM M_CUS T1 ,T_ITM_EVL T2
WHERE T1.CUS_ID = 'CUS_0002'
AND T1.CUS_ID = T2.CUS_ID;
-- 아우터-조인
SELECT T1.CUS_ID ,T1.CUS_NM ,T2.CUS_ID ,T2.ITM_ID ,T2.EVL_LST_NO
FROM M_CUS T1 ,T_ITM_EVL T2
WHERE T1.CUS_ID = 'CUS_0002'
AND T1.CUS_ID = T2.CUS_ID(+);
이너-조인을 하면 아무 결과도 조회되지 않는다. 하지만 아우터-조인을 하면 'CUS_0002' 고객의 정보가 조회되는 것을 확인할 수 있다.
위 아우터-조인 조건은 'T1.CUS_ID = T2.CUS_ID(+)'이다.
- 조인 조건에 '(+)' 표시가 있으니 아우터-조인이다.
- '(+)' 표시가 붙은 T2(=T_ITM_EVL)는 아우터-조인의 '참조 데이터 집합'이다.
- '(+)' 표시가 없는 T1(=M_CUS)은 아우터-조인의 '기준 데이터 집합'이다.(이때, 기준 데이터 집합의 참조쪽 결과는 NULL로 값이 채워진다.)
- : 기준 데이터 집합은 조인에 성공 못 해도 결과값이 나온다.
예제 ) 아우터-조인, 한 명은 평가가 있음, 한 명은 평가가 없음
SELECT T1.CUS_ID, T1.CUS_NM ,T2.CUS_ID, T2.ITM_ID, T2.EVL_LST_NO
FROM M_CUS T1 ,T_ITM_EVL T2
WHERE T1.CUS_ID IN ('CUS_0002', 'CUS_0011')
AND T1.CUS_ID = T2.CUS_ID(+) ORDER BY T1.CUS_ID;
조인 조건을 보면 'T1.CUS_ID = T2.CUS_ID(+)' 이므로 M_CUS(T1) 가 '기준 데이터 집합'임을 알 수 있고, T_ITM_EVL(T2)이 '참조 데이터 집합'임을 알 수 있다. 따라서 '기준 데이터 집합'인 M_CUS(T1)에서는 조인 조건에 만족하지 않는 결과값도 모두 출력되는 것을 확인할 수 있다.
위 결과 값이 나오는 과정은 다음과 같다.
- 1번은 M_CUS(T1)에서 두 건의 고객만 필터 된 데이터 집합이다.
- 2번은 T_ITM_EVL(T2)의 데이터 집합이다.
- 3번은 T1.CUS_ID = T2.CUS_ID(+) 조건으로 아우터-조인을 수행한 최종 결과다.: 조인 조건에 (+)가 붙은 2번은 참조 데이터 집합이다.: 참조 데이터 집합에 CUS_0011은 존재하므로 정상적으로 조인이 된다.
- : 참조 데이터 집합에 CUS_0002는 존재하지 않음으로 NULL값으로 채워진다.
- : 조인 조건에 (+)가 붙지 않은 1번은 기준 데이터 집합이다.
요약하자면
- 기준 데이터 집합은 조인 조건에 (+)가 없는 쪽이다.
- 참조 데이터 집합은 조인 조건에 (+)가 표시된 쪽이다.
- 기준 데이터 집합은 조인 성공 여부와 상관없이 모두 나온다.(단, 필터 조건은 만족해야한다.)
- OUTER-JOIN의 필터 조건
아우터-조인에서는 '참조 데이터 집합'의 필터 조건에도 '(+)'표시를 추가해야한다.
예제) 필터 조건에 (+)표시 유무에 따른 결과 비교
SELECT T1.CUS_ID, T1.CUS_NM ,T2.CUS_ID, T2.ITM_ID ,T2.EVL_LST_NO, T2.EVL_DT
FROM M_CUS T1 ,T_ITM_EVL T2
WHERE T1.CUS_ID IN ('CUS_0073')
AND T1.CUS_ID = T2.CUS_ID(+)
AND T2.EVL_DT >= TO_DATE('20170201', 'YYYYMMDD')
AND T2.EVL_DT < TO_DATE('20170301', 'YYYYMMDD');
'T1.CUS_ID = T2.CUS_ID(+)' 아우터-조인을 사용했음에도 불구하고, 아무것도 조회되지 않은 것을 확인할 수 있다.이번에는 필터 조건'T2.EVL_DT >= TO_DATE('20170201', 'YYYYMMDD')' 와 'T2.EVL_DT < TO_DATE('20170301', 'YYYYMMDD')'쪽에 '(+)' 표시를 추가해보도록 한다.
SELECT T1.CUS_ID, T1.CUS_NM ,T2.CUS_ID, T2.ITM_ID ,T2.EVL_LST_NO, T2.EVL_DT
FROM M_CUS T1 ,T_ITM_EVL T2
WHERE T1.CUS_ID IN ('CUS_0073')
AND T1.CUS_ID = T2.CUS_ID(+)
AND T2.EVL_DT(+) >= TO_DATE('20170201', 'YYYYMMDD')
AND T2.EVL_DT(+) < TO_DATE('20170301', 'YYYYMMDD');
필터 조건 T2.EVL_DT에 (+) 표시를 추가하니 1 건 조회되었다.
이는 참조 데이터 집합 쪽 필터 조건에 '(+)' 표시 때문에 결과값이 다르게 출력된다.
- 참조 쪽 필터 조건에 (+) 사용 : 아우터-조인 전에 필터 조건이 사용된다.
- 참조 쪽 필터 조건에 (+) 미사용 : 아우터-조인 후, 조인 결과에 필터 조건이 사용된다.
위를 참고하면, 처음 쿼리는 참조 데이터 집합 쪽 필터 조건에 (+) 미사용한 경우에 해당하므로 이우터-조인 후, 조인 결과에 필터 조건이 사용되기때문에 아무것도 출력되지 않는다. 이는 결과적으로 이너-조인과 같다.
참조 데이터 집합 쪽 필터 조건에 (+) 한 경우는 아우터-조인 전에 필터 조건이 사용되기 때문에 각 데이터 집합에서 필터 조건이 된 후 아우터-조인이 된다.
- 실행이 불가능한 OUTER-JOIN
아우터-조인에서 '(+)'표시가 된 참조 데이터 집합은 두 개 이상의 기준 데이터 집합을 동시에 가질 수 없다. (11g 기준으로는 불가능하지만 상위 버전에서는 가능할 수도 있다.)
예제) 불가능한 아우터-조인
SELECT T1.CUS_ID, T2.ITM_ID, T1.ORD_DT, T3.ITM_ID, T3.EVL_PT
FROM T_ORD T1 ,T_ORD_DET T2 ,T_ITM_EVL T3
WHERE T1.ORD_SEQ = T2.ORD_SEQ
AND T1.CUS_ID = 'CUS_0002'
AND T1.ORD_DT >= TO_DATE('20170122', 'YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170123', 'YYYYMMDD')
AND T3.CUS_ID(+) = T1.CUS_ID
AND T3.ITM_ID(+) = T2.ITM_ID;
11g에서는 ORA-01417 : a table may be outer joined to at most one other table 과 같은 에러가 발생한다. 그러나 위와같이 18c에서는 참조 데이터 집합이 기준 데이터 집합을 두 개 이상가져도 정상적으로 결과가 출력되는 것을 확인할 수 있다.
11g기준으로 설명을 드리면, T3(아이템평가)가 T1(주문), T2(주문상세)와 동시에 아우터-조인을 하고 있다. 이와 같은 아우터-조인에서 참조 데이터 집합(T3)은 기준 데이터 집합을 동시에 두 개 이상 가질 수 없다.
이를 해결하려면 인라인-뷰를 사용해야한다. T1과 T2의 조인 결과를 인라인-뷰로 처리하면 인라인-뷰를 하나의 데이터 집합으로 받아들이므로 T3와 아우터-조인을 할 수 있다.
예제) 인라인-뷰를 사용한 아우터-조인
SELECT T0.CUS_ID, T0.ITM_ID, T0.ORD_DT ,T3.ITM_ID, T3.EVL_PT
FROM ( SELECT T1.CUS_ID, T2.ITM_ID, T1.ORD_DT
FROM T_ORD T1 ,T_ORD_DET T2
WHERE T1.ORD_SEQ = T2.ORD_SEQ
AND T1.CUS_ID = 'CUS_0002'
AND T1.ORD_DT >= TO_DATE('20170122', 'YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170123', 'YYYYMMDD')
) T0
,T_ITM_EVL T3
WHERE T3.CUS_ID(+) = T0.CUS_ID
AND T3.ITM_ID(+) = T0.ITM_ID
ORDER BY T0.CUS_ID;
예제) ANSI 구문을 사용해 불가능한 아우터-조인 해결
SELECT T1.CUS_ID, T2.ITM_ID, T1.ORD_DT ,T3.ITM_ID, T3.EVL_PT
FROM T_ORD T1
INNER JOIN T_ORD_DET T2
ON (T1.ORD_SEQ = T2.ORD_SEQ
AND T1.CUS_ID = 'CUS_0002'
AND T1.ORD_DT >= TO_DATE('20170122', 'YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170123', 'YYYYMMDD'))
LEFT OUTER JOIN T_ITM_EVL T3
ON (T3.CUS_ID = T1.CUS_ID
AND T3.ITM_ID = T2.ITM_ID);
ANSI 구문을 사용하면 타 DBMS도 ANSI 표준을 지원하기 때문에 이관을 고려하면 괜찮은 방법이지만, 오라클에서는 성능을 위한 힌트가 잘 안 먹히는 경우가 있다고 한다.
- OUTER-JOIN이 포함된 여러 테이블의 조인
하나의 SQL에서 아우터-조인과 이너-조인을 동시에 사용할 때 주의할 점이 있다.
예제) 아우터-조인과 이너-조인을 동시에 사용하는 SQL
SELECT T1.CUS_ID, T2.ORD_SEQ, T2.ORD_DT, T3.ORD_SEQ, T3.ITM_ID
FROM M_CUS T1 ,T_ORD T2 ,T_ORD_DET T3
WHERE T1.CUS_ID = 'CUS_0073'
AND T1.CUS_ID = T2.CUS_ID(+)
AND T2.ORD_DT(+) >= TO_DATE('20170122', 'YYYYMMDD')
AND T2.ORD_DT(+) < TO_DATE('20170123', 'YYYYMMDD')
AND T3.ORD_SEQ = T2.ORD_SEQ;
위 쿼리는 아우터-조인을 사용하고 있음에도 불구하고 조회되는 데이터가 한 건도 없다.
그 이유는 아우터-조인의 결과에 이너-조인( T3.ORD_SEQ = T2.ORD_SEQ)을 수행하는데 아우터-조인의 결과값 중 T2.ORD_SEQ가 NULL값이 나오기 때문에 아우터-조인을 했음에도 데이터가 한 건도 없게 된 것이다.
조인 결과가 나오게 하려면 'T3.ORD_SEQ(+) = T2.ORD_SEQ'와 같이 아우터-조인으로 변경해야 한다.
위와 같이 여러 테이블이 조인될 때, 아우터-조인이 수행된 참조 데이터 집합은 기준 집합이 되어서 다른 테이블과 아우터-조인을 해야한다.
- OUTER-JOIN의 응용
아우터-조인은 조인에 성공하지 못해도 기준 데이터 집합은 무조건 조회되는 특징이 있다. 이와 같은 특징은 분석 리포트에서 실적이 없는 마스터도 결과에 포함시킬 때 유용하다.
예제) 고객ID별 주문건수, 주문이 없는 고객도 나오도록 처리
SELECT T1.CUS_ID ,COUNT(*) ORD_CNT_1 ,COUNT(T2.ORD_SEQ) ORD_CNT_2
FROM M_CUS T1 ,T_ORD T2
WHERE T1.CUS_ID = T2.CUS_ID(+)
AND T2.ORD_DT(+) >= TO_DATE('20170101', 'YYYYMMDD')
AND T2.ORD_DT(+) < TO_DATE('20170201', 'YYYYMMDD')
GROUP BY T1.CUS_ID
ORDER BY COUNT(*), T1.CUS_ID;
고객별 1월의 주문 건수를 구할 때 주문이 없는 고객은 주문건수가 0으로 조회되어야한다. 다음과 같이 아우터-조인을 사용하면 주문이 없는 고객도 모두 조회가 가능하다.
여기서 주문 건수를 구하기 위해 COUNT 집계함수를 사용하였는데, 1월에 주문이 하나도 없는 고객을 구하기 위해서는 NULL을 포함하여 COUNT하는 COUNT(*)이 아닌 COUNT(T2.ORD_SEQ)를 사용해야한다.
예제) 'PC, ELEC' 아이템 유형의 아이템별 주문수량 조회 (주문이 없어도 0으로 나와야 한다.)
SELECT T1.ITM_ID, T1.ITM_NM, NVL(T2.ORD_QTY, 0)
FROM M_ITM T1
,( SELECT B.ITM_ID, SUM(B.ORD_QTY) ORD_QTY
FROM T_ORD A ,T_ORD_DET B
WHERE A.ORD_SEQ = B.ORD_SEQ
AND A.ORD_ST = 'COMP'
AND A.ORD_DT >= TO_DATE('20170101', 'YYYYMMDD')
AND A.ORD_DT < TO_DATE('20170201', 'YYYYMMDD')
GROUP BY B.ITM_ID ) T2
WHERE T1.ITM_ID = T2.ITM_ID(+)
AND T1.ITM_TP IN ('PC', 'ELEC')
ORDER BY T1.ITM_TP, T1.ITM_ID;
인라인-뷰를 사용해 실적 데이터를 M_ITM 테이블의 PK 단위인 ITM_ID로 'GROUP BY' 한 후에 조인하고 있다.
아우터-조인의 특징을 정리
- 아우터-조인은 기준 데이터 집합과 참조 제이터 집합으로서 조인이 이루어진다.
- 참조 데이터 집합은 조인 조건에 (+)가 표시된 쪽이며 반대쪽은 기준 데이터 집합이 된다.
- 기준 데이터 집합은 조인 조건을 만족하지 않아도 필터 조건만 만족하면 결과가 나온다.
- (이 때, 참조 데이터 집합의 결과는 NULL 값으로 채워진다.)
- 참조 데이터 집합의 필터 조건에 (+)를 표시하면 아우터-조인 전에 필터가 된다.
- 참조 데이터 집합의 필터 조건에 (+)를 표시하지 않으면 아우터-조인 후 필터가 된다.
- 일반적으로 참조 데이터 집합의 필터 조건에는 (+)를 표시한다.
- 참조 데이터 집합이 다른 집합과 조인될 때는 기준 집합으로서 아우터-조인해야 한다.
'SQL' 카테고리의 다른 글
| [SQL]카테시안조인(CARTESIAN JOIN) (0) | 2021.02.07 |
|---|