[SQL BOOSTER] 3-2. OUTER JOIN

📚 SQL BOOSTER (유일환, DBian)

 

CH3. JOIN

3-2. OUTER JOIN

3-2-1. OUTER JOIN 이해하기

Outer Join은 조인 조건을 만족하지 않은 데이터도 결과에 나온다.

 

Outer Join의 데이터 집합

기준 데이터 집합 : Outer Join의 기준이 되는 집합, Outer 집합이라고도 한다.

참조 데이터 집합 : Outer Join의 참조가 되는 집합, Inner 집합이라고도 한다.

 

기준 데이터 집합(Outer 집합)은 조인 조건을 만족하지 않아도 모두 결과에 포함된다.

단, 필터 조건은 만족해야 한다.

 

Outer Join을 사용하려면 참조 데이터 집합쪽의 조인 조건 컬럼에 (+) 표시를 추가하면 된다.

ANSI로는 참조 데이터 집합을 LEFT OUTER JOIN한다.

 

그럼 먼저 Inner Join과 Outer Join을 비교해보자.

 

예시1. 고객 'CUS_0002'의 고객(M_CUS) 정보와 아이템 평가(T_ITM_EVL) 정보 구하기

 

고객(M_CUS) 데이터 집합은 고객 'CUS_0002'의 정보를 가지고 있다.

SELECT *
FROM m_cus cus
WHERE cus.cus_id = 'CUS_0002';

 

 

아이템 평가(T_ITM_EVL) 데이터 집합은 고객 'CUS_0002'의 정보를 가지고 있지 않다.

SELECT *
FROM t_itm_evl evl
WHERE evl.cus_id = 'CUS_0002';

 

 

이때 조인 조건(cus.cus_id = evl.cus_id)에 대한 Inner Join과 Outer Join의 결과가 어떻게 달라지는지 살펴보자.

 

(Inner Join, Oracle)

SELECT cus.cus_id, cus.cus_nm, evl.cus_id, evl.itm_id, evl.evl_lst_no
FROM m_cus cus,
     t_itm_evl evl
WHERE cus.cus_id = 'CUS_0002'
AND   cus.cus_id = evl.cus_id;

 

Inner Join은 아무 결과도 조회되지 않는다.

 

(Outer Join, Oracle)

SELECT cus.cus_id, cus.cus_nm, evl.cus_id, evl.itm_id, evl.evl_lst_no
FROM m_cus cus,
     t_itm_evl evl
WHERE cus.cus_id = 'CUS_0002'
AND   cus.cus_id = evl.cus_id (+);

 

반면 Outer Join은 'CUS_0002' 고객의 정보가 조회된다.

 

이와 같이 Outer Join은 조인 조건을 만족하지 못한 데이터도 결과에 포함되어 나온다.

이때 기준 데이터 집합의 참조쪽 결과는 값이 NULL로 채워지게 된다. 

 

예시1의 ANSI 쿼리는 다음과 같다.

 

(Inner Join, ANSI)

SELECT cus.cus_id, cus.cus_nm, evl.cus_id, evl.itm_id, evl.evl_lst_no
FROM m_cus cus
JOIN t_itm_evl evl
ON cus.cus_id = evl.cus_id
WHERE cus.cus_id = 'CUS_0002';

 

(Outer Join, ANSI)

SELECT cus.cus_id, cus.cus_nm, evl.cus_id, evl.itm_id, evl.evl_lst_no
FROM m_cus cus
LEFT JOIN t_itm_evl evl
ON cus.cus_id = evl.cus_id
WHERE cus.cus_id = 'CUS_0002';

 

예시2. 두 명의 고객('CUS_0002', 'CUS_0011')을 아이템 평가(T_ITM_EVL)와 Outer Join하기

 

(나의 풀이, ANSI)

SELECT cus.cus_id, cus.cus_nm, evl.cus_id, evl.itm_id, evl_lst_no
FROM m_cus cus
LEFT JOIN t_itm_evl evl
ON cus.cus_id = evl.cus_id
WHERE cus.cus_id IN ('CUS_0002', 'CUS_0011')
ORDER BY 1;

 

(교재 풀이, Oracle)

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;

 

 

위 SQL이 처리되는 과정은 다음과 같다.

1. m_cus에서 'CUS_0002', 'CUS_0011' 두 건의 고객이 필터링 된다.

2. cus.cus_id = evl.cus_id을 조건으로 Outer Join을 수행한다.

• m_cus는 기준 데이터 집합(Outer 집합)이다.
• t_itm_evl은 참조 데이터 집합(Inner 집합)이다.
• 참조 데이터 집합에 'CUS_0002'는 존재하지 않으므로 NULL 값으로 채워진다.

 

3-2-2. OUTER JOIN의 필터 조건

Outer Join에서는 참조 데이터 집합의 필터 조건에도 (+) 표시를 추가해야 한다.

 

참조 데이터 집합의 필터 조건에 (+) 사용 시 Outer Join 전에 필터 조건이 사용된다.

참조 데이터 집합의 필터 조건에 (+) 미사용 시 Outer Join 후, 조인 결과에 필터 조건이 사용된다.

 

예시1. 필터 조건에 (+) 표시 유무에 따른 결과 비교

고객('CUS_0073')을 아이템평가(T_ITM_EVL)와 Outer Join하기

 

(참조 쪽 필터 조건에 (+) 표시 없음)

SELECT cus.cus_id, cus.cus_nm, evl.cus_id, evl.itm_id, evl.evl_lst_no, evl.evl_dt
FROM m_cus cus
    , t_itm_evl evl
WHERE cus.cus_id IN ('CUS_0073')
AND cus.cus_id = evl.cus_id(+)
AND evl.evl_dt >= to_date('20170201', 'yyyymmdd')
AND evl.evl_dt < to_date('20170301', 'yyyymmdd');

 

 

위 쿼리에서는 Outer Join의 참조 데이터 집합 쪽 필터 조건에 (+) 표시가 없다.

그러므로 Outer Join이 먼저 처리된 후, 그 조인 결과에 필터 조건이 적용된다.

Outer Join이 이루어지면 참조 데이터 집합의 EVL_DT는 NULL이 된다.

이 NULL 값에 대해 6, 7번 라인의 필터 조건을 적용하면 이를 만족하는 데이터가 없으므로 결국 조회되는 데이터가 없다.

이는 결과적으로 Inner Join과 같다.

 

(참조 쪽 필터 조건에 (+) 표시 있음)

SELECT cus.cus_id, cus.cus_nm, evl.cus_id, evl.itm_id, evl.evl_lst_no, evl.evl_dt
FROM m_cus cus
    , t_itm_evl evl
WHERE cus.cus_id IN ('CUS_0073')
AND cus.cus_id = evl.cus_id(+)
AND evl.evl_dt(+) >= to_date('20170201', 'yyyymmdd')
AND evl.evl_dt(+) < to_date('20170301', 'yyyymmdd');

 

 

위와 같이 Outer Join 시에는 참조 쪽 필터 조건에 (+) 표시를 해줘야 원하는 데이터가 조회된다.

 

위의 예시 1을 ANSI로 풀이하면 다음과 같다.

 

(WHERE 조건에 참조 데이터 집합의 필터 조건을 준 경우(ANSI))

SELECT cus.cus_id, cus.cus_nm, evl.cus_id, evl.itm_id, evl.evl_lst_no, evl.evl_dt
FROM m_cus cus
LEFT JOIN t_itm_evl evl
ON cus.cus_id = evl.cus_id
WHERE cus.cus_id = 'CUS_0073'
AND evl.evl_dt >= to_date('20170201', 'yyyymmdd')
AND evl.evl_dt < to_date('20170301', 'yyyymmdd');

 

 

(JOIN 조건에 참조 데이터 집합의 필터 조건을 준 경우(ANSI))

SELECT cus.cus_id, cus.cus_nm, evl.cus_id, evl.itm_id, evl.evl_lst_no, evl.evl_dt
FROM m_cus cus
LEFT JOIN t_itm_evl evl
ON cus.cus_id = evl.cus_id
AND evl.evl_dt >= to_date('20170201', 'yyyymmdd')
AND evl.evl_dt < to_date('20170301', 'yyyymmdd')
WHERE cus.cus_id = 'CUS_0073';

 

 

ANSI로 Outer Join할 때에는 참조 데이터 집합 쪽 필터 조건을 Join 조건에 주어야 원하는 결과가 나온다.

 

3-2-3. 실행이 불가능한 OUTER JOIN

Oracle의 Outer Join에서 참조 데이터 집합은 기준 데이터 집합을 동시에 두 개 이상 가질 수 없다.

 

예시1. 실행이 불가능한 Outer Join

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;

 

위의 9, 10 라인을 보면 T3(아이템평가)는 T1(주문), T2(주문상세)와 동시에 Outer Join을 하고 있다.

위 쿼리를 실행하면 다음과 같은 에러가 발생한다.

 

"ORA-01417: a table may be outer joined to at most one other table Position: 310"

 

이를 해결하기 위한 두 가지 방법을 알아보자.

 

첫 번째는 인라인-뷰를 사용하는 방법이다.

 

예시2. 인라인-뷰를 사용해 불가능한 Outer Join 해결

SELECT T1.cus_id, T1.itm_id, T1.ord_dt, T2.itm_id, T2.evl_pt
FROM (SELECT ord.cus_id, det.itm_id, ord.ord_dt
      FROM t_ord ord,
           t_ord_det det
      WHERE ord.ord_seq = det.ord_seq
        AND ord.cus_id = 'CUS_0002'
        AND ord.ord_dt >= to_date('20170122', 'yyyymmdd')
        AND ord.ord_dt < to_date('20170123', 'yyyymmdd')) T1,
     t_itm_evl T2
WHERE T2.cus_id(+) = T1.cus_id
  AND T2.itm_id(+) = T1.itm_id
ORDER BY 1,2;

 

두 번째는 ANSI 표준 SQL을 사용하는 방법이다.

 

예시3. ANSI 구문을 사용해 불가능한 Outer Join 해결

SELECT ord.cus_id, det.itm_id, ord.ord_dt, evl.itm_id, evl.evl_pt
FROM t_ord ord
JOIN t_ord_det det
ON ord.ord_seq = det.ord_seq
LEFT JOIN t_itm_evl evl
ON ord.cus_id = evl.cus_id
AND det.itm_id = evl.itm_id
WHERE ord.cus_id = 'CUS_0002'
AND ord.ord_dt >= to_date('20170122', 'yyyymmdd')
AND ord.ord_dt < to_date('20170123', 'yyyymmdd')
ORDER BY 1,2;

 

단, Oracle에서 ANSI 구문을 사용하면 성능을 위한 힌트가 잘 안 먹는 경우가 있다는 것을 참고하자.

 

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

하나의 SQL에서 Outer Join과 Inner Join을 동시에 사용할 때 주의할 점이 있다.

예시를 통해 살펴보자.

 

예시1. Outer Join과 Inner Join을 동시에 사용하는 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;

 

 

위 쿼리를 수행하면 조회되는 데이터가 없다.

그 이유는 무엇일까?

쿼리 수행 과정을 차례대로 살펴보자.

먼저 T1(고객)과 T2(주문)가 Outer Join을 하고 있다.

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;

 

 

T2(주문) 데이터 집합에는 고객 'CUS_0073'의 정보가 없기 때문에 Outer Join의 참조 데이터 집합쪽 결과는 NULL이 된다.

 

다시 원래 쿼리를 살펴보면 9번 라인에서 T2(주문)과 T3(주문상세)가 ORD_SEQ 컬럼으로 Inner Join을 수행하고 있다.

그런데 T1과 Outer Join 수행 후 T2의 ORD_SEQ는 NULL값을 가진다.

NULL값은 비교를 할 수 없다.

따라서 결과적으로 조회되는 데이터가 없게 된다.

 

조인 결과가 나오게 하려면 T3도 T2에 Outer Join을 걸어줘야 한다.

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;

 

 

(ANSI 풀이)

SELECT cus.cus_id, ord.ord_seq, ord.ord_dt, det.ord_seq, det.itm_id
FROM m_cus cus
LEFT JOIN t_ord ord
ON cus.cus_id = ord.cus_id
AND ord.ord_dt >= to_date('20170122', 'yyyymmdd')
AND ord.ord_dt < to_date('20170123', 'yyyymmdd')
LEFT JOIN t_ord_det det
ON ord.ord_seq = det.ord_seq
WHERE cus.cus_id = 'CUS_0073';

 

위 내용을 정리하면, 여러 테이블이 조인될 때 Outer Join이 수행된 참조 데이터 집합은 기준 집합이 되어서 다른 테이블과 Outer Join해야 한다.

 

3-2-5. OUTER JOIN의 응용

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

이와 같은 특징은 분석 리포트에서 실적이 없는 마스터도 결과에 포함시킬 때 유용하다.

 

예시1. 고객별 1월의 주문 건수를 구하는 SQL (주문이 없는 고객은 주문 건수가 0으로 조회되어야 한다.)

SELECT cus.cus_id, count(*) AS cnt, count(ord.ord_seq) AS ord_cnt
FROM m_cus cus
LEFT JOIN t_ord ord
ON cus.cus_id = ord.cus_id
AND ord.ord_dt >= to_date('20170101', 'yyyymmdd')
AND ord.ord_dt < to_date('20170201', 'yyyymmdd')
GROUP BY cus.cus_id
ORDER BY 1;

 

 

주문 건수를 구하기 위해 COUNT() 집계 함수를 사용하는데, 두 번째 컬럼은 COUNT(*)을 수행했고, 세 번째 컬럼은 COUNT(ord.ord_seq)를 수행했다.

고객 'CUS_0001'의 결과를 보면, COUNT(*)의 결과는 1건이고, COUNT(ord.ord_seq)의 결과는 0건이다.

이 중에 필요한 결과는 COUNT(ord.ord_seq)을 사용한 결과다.

왜냐하면 고객 'CUS_0001'은 1월에 주문이 하나도 없는 고객이기 때문이다.

 

이처럼 Outer Join과 COUNT() 집계 함수를 동시에 사용할 때에는 항상 주의가 필요하다.

COUNT() 대상에 따라 결과가 달라지기 때문이다.

COUNT() 대상이 실적의 건수라면 위의 COUNT(ord.ord_seq)와 같이 참조 테이블의 컬럼을 사용해야 한다.

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

 

예시2. 'PC, ELEC' 아이템 유형의 아이템별 1월의 주문 수량 구하기 (주문이 없어도 0으로 나와야 한다.)

SELECT T1.ITM_ID, T1.ITM_NM, NVL(T2.ORD_QTY,0) AS ORD_QTY
FROM M_ITM T1
    , (SELECT B.ITM_ID, SUM(ORD_QTY) AS 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 1;

 

 

인라인-뷰를 사용해 실적 데이터를 M_ITM 테이블의 PK 단위인 ITM_ID로 Group By한 후에 조인하고 있다.

이와 같이 SQL을 작성하면 가독성이 좋아지고, 결과적으로 유지 보수에도 도움이 된다.