SQL BOOSTER - OUTER JOIN

 

1. OUTER JOIN이란 

 

• 이너-조인은 조인 조건에 만족한 데이터만 결과에 나올 수 있다. 반면에 아우터 조인은 조인 조건에 만족하지 않은 데이터도 결과에 나온다.
• 아우터-조인을 설명하기 위해 기준 데이터 집합과 탐조 데이터 집합이란 용어를 정의하자
  • 기준 데이터 집합: 아우터-조인의 기준이 되는 집합
  • 참조 데이터 집합: 아우터-조인의 참조가 되는 집합
• 기준 데이터 집합은 조인 조건을 만족하지 않아도 모두 결과에 포함된다.
• 아우터-조인을 사용하려면, 조인 조건 컬럼 한쪽에 +를 추가하면 된다. (+가 있는 쪽이 참조 데이터 집합이다.)

--이너조인
SELECT T1.CUS_ID, T1.CUS_NM, T2.CUS_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.EVL_LST_NO 
FROM M_CUS T1, T_ITM_EVL T2
WHERE T1.CUS_ID = 'CUS_0002' 
AND T1.CUS_ID = T2.CUS_ID(+);

 

- 조인 조건에 +표시가 있으니 아우터-조인이다.

- + 표시가 붙은 T2는 아우터 조인의 참조 데이터 집합이다.

- + 표시가 없는 T1은 아우터 조인의 기준 데이터 집합이다. 

   > 기준 데이터 집합은 조인에 성공 못 해도 결괏값이 나온다. (이때, 참조쪽 결과는  null로 채워짐)

 

SQL BOOSTER

SELECT T1.CUS_ID, T1.CUS_NM, T2.CUS_IT, 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_IT = T2.CUS_ID(+)
ORDER BY T1.CUS_ID;

 

SQL BOOSTER

---

 

2. OUTER-JOIN 필터 조건

 

아우터-조인에는 참조 데이터 집합의 필터 조건에도 + 표시를 추가해야 한다. 아래 두 개 SQL을 살펴보자 

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');

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
AND T1.CUS_ID = T2.CUS_ID(+)
AND T2.EVL_DT(+) = TO_DATE('20170201','YYYYMMDD')
AND T2.EVL_DT(+) < TO_DATE ('20170301','YYYYMMDD');

• 아우터-조인을 하면서, T2 컬럼쪽에 +를 사용했다 (WHERE 문)
• 왼쪽의 SQL은 조회되는 결과가 없다. 오른쪽만 결과가 나온다. 이는 참조 데이터 집합 쪽 필터 조건에 +표시 때문이다.
  • 참조 쪽 필터 조건에 + 사용 : 아우터-조인 전에 필터 조건에 사용된다.
  • 팜조 쪽 필터 조건에 + 미사용 : 아우터-조인 후 조인 결과에 필터 조건이 사용된다.
• 위쪽 SQL은 아우터 조인이 먼저 처리된 후 필터가 적용된다. -> 아우터 조인이 이루어지면, 참조 데이터 집합의 EVL_DT는 NULL이된다. 그렇다면, 다음 AND 라인을 만족하는 데이터가 없음으로, 조회결과가 없다. 결과적으로 이너조인과 같아진다. 
• 기본적으로 아우터 조인을 사용할 때는 + 필터 조건을 표시해야한다. 이게 필요없다면, 이너조인을 쓰는게 맞다.

---

3. 실행 불가능한 OUTER-JOIN

 

아우터-조인에서 + 표시가 된 참조 데이터 집합은 두 개 이상의 기준 데이터 집합을 동시에 가질 수 없다.

 

SELECT T1.CUS_ID, T2.ITM_ID, T1.ORD_DT, T3.EVL_PT
FROM T_ORD T1, T_ROD_DET T2, T_ITM_EVL T3
WHERE T1.ORD_SEQ = T2.ORD_SEQ
-- 생략

AND T3.CUS_ID(+) = T1.CUS_ID
AND T3.ITM_ID(+) = T2.ITM_ID;

 

위 SQL은 불가능한 아우터 조인의 예시이다. 참조 데이터 집합은 기준 데이터 집합을 동시에 두 개 이상 가질 수 없다.

이를 해결하려면, 인라인 뷰를 하나 만들어야한다. 

 

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;

---

 

4. OUTER-JOIN이 포함된 여러 테이블의 조인

 

• 아우터-조인과 이너-조인을 동시에 사용할 때 주의할 점을 알아보자

SELECT T1.CUS_ID, T2.ORD_SEQ, 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;

• 위 SQL의 조인 조건은 T1과 T2를 아우터 조인하고, T2와 T3를 이너 조인으로 처리하고 있다.
• 아우터 조인이 있음에도 불구하고, 조회되는 데이터가 한건도 없다. 원인은 다음과 같다.

SQL BOOSTER

• 왼쪽 1번 결과는 T1과 T2가 아우터 조인 처리된 결과이다. T2테이블이 null로 채워져있다.
• 이를 다시 T3와 이너조인하면, 결과가 있을 수 없다. 
• 조인 결과가 나오게하려면, T3.ORD_SEQ(+) = T2.ORD_SEQ와 같이 아우터 조인으로 변경해야한다.

 

* 여러 테이블이 조인될 때, 아우터 조인이 수행된 참조 데이터 집합은 기준 집합이 되어서 다른 테이블과 아우터 조인 해야한다.

 

---

5. OUTER-JOIN 응용

 

아우터-조인은 조인에 성공하지 못해도 기준 데이터 집합은 무조건 조회되는 특징이 있다. 

 

해당 특성은 분석 리포트에서 실적이 없는 마스터도 결과에 포함시킬 때 유용하다!!

 

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;

 

고객 테이블은 기준 집합으로 아우터 조인 했기 때문에, 주문이 없는 고객도 모두 조회 가능하다.

 

주문 건수를 구하기 위해 COUNT 집계함수를 사용하는데, 2번 라인은 COUNT(*)을 수행했고, 3번 라인은 COUNT(T2.ORD_SEQ)를 사용했다. 

 

'CUS_0001' 고객의 경우 COUNT(*)의 결과는 1이고 COUNT(T2.ORD_SEQ)의 결과는 0이다

이 중에 필요한 결과는 COUNT(T2.ORD_SEQ)를 사용한 경우다 '. 'CUS_0001' 고객은 1월에 주문이 하나도 없는 고객이기 때문이다.

아우터 조인과 COUNT 집계함수를 동시에 사용할 때는 항상 주의가 필요하다.

COUNT 대상에 따라 결과가 다르기 때문이다.

COUNT 대상이 실적의 건수라면 COUNT(T2.ORD_SEQ)와 같이 참조 테이블의 칼럼을 사용하고,

COUNT 대상이 NULL값이 포함한 조인 결과 자체의 건수라면 COUNT(*)을 사용해야 한다.

 

이번에는 아우터-조인을 활용해서, 아이템별 특정 월의 주문 건수를 구하는 SQL을 만들어 보자 

 

SELECT T1.ITM_ID, T1.ITM.NM, NVL(T2.ORD_QTY,0)
FROM M_IMT 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_D >= 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한 후에 조인하고 있다. 
• 이처럼 SQL을 작성하면 가독성이 좀 더 좋다. 가독성이 좋은 SQL은 유지보수에 용이하다.
• *모든 아이템 중에 아이템이 판매된 건수 

---

정리 

 

• 아우터-조인은 기준 데이터 집합과 참조 데이터 집합으로서 조인이 이루어진다.
• 참조 데이터 집합은 조인 조건에 +가 표시된 쪽이다.
• 기준 데이터 집합은 조인 조건을 만족하지 않아도 필터 조건만 만족하면 결과가 나온다. 
• 참조 데이터 집합의 필터 조건에 +표시를 해야 아우터 조인 전에 필터가 된다. (붙이지 않으면, 조인 후 필터)
• 일반적으로 참조 데이터 집합의 필터 조건에는 + 표시를 한다.
• 참조 데이터 집합이 다른 집합과 조인될 때는 기준 집합으로서 아우터-조인 해야한다. 

 

---

https://www.yes24.com/Product/Goods/82818767

SQL BOOSTER - 예스24