4. OUTER 조인시의 처리

OUTER조인은 어떤 집합을 기준으로 해서 조인되는 다른 집합과의 연결에 실패했더라도 그 결과를 추출하는 조인을 말한다. 이러한 형태의 조인은 실무에서 자주 등장하기도 하며 그 처리를 위해 우리는 약간의 주의를 기울일 필요가 있다.
아래의 예제는 ANSI SQL OUTER JOIN 비교 예제이다.(Altibase, Oracle)

Altibase iSQL> select A.*, B.*, C.* 
                      from out1B b left outer join out1A a on A.C1 = B.C1 AND A.C2 = B.C2, out1C c 
                      where c.c1 = 11;
Oracle SQL> select  A.*, B.*, C.* 
                     from out1A a, out1B b, out1C c 
                     where a.c1(+) = b.c1 AND a.c2(+) = b.c1 and c.c1 = 11;
 
Altibase iSQL> select A.*, B.*, C.* 
                      from OUT1A A left outer join OUT1B B on B.C1 = A.C1 left outer join out1C c on c.c1 = a.c1;
Oracle SQL> select A.*, B.*, C.* 
                     from out1A a, out1B b, out1C c 
                     where b.c1(+) = a.c1 and c.c1(+) = a.c1;
 
Altibase iSQL> select k1.*, k2.*, k3.* 
                      from k3 left outer join k1 on k1.c3 = k3.c3  and k1.c4 = k3.c4, k2 
                      where k1.c1 = k2.c1 and k1.c2 = k2.c2   and k1.c1 = 2;
Oracle SQL> select k1.*, k2.*, k3.*  
                     from k1,k2,k3 
                     where k1.c1 = k2.c1 and k1.c2 = k2.c2 and k1.c3(+) = k3.c3 and k1.c4(+) = k3.c4 and k1.c1 = 2;

4.1. OUTER 조인과 조인 실패의 원인

• TABLE1,TABLE2에 대해 COL1을 조인키로하여 조인하되 TABLE1에서 COL3='B'인 조건을 만족하는 모든 로우를 구하라.
  

• 일반 조인
  아래와 같은 일반조인은 X.COL1=Y.COL1 조건을 만족하는 로우만을 가져오며
  TABLE2의 COL1 값에는 14,15의 값이 없으므로
  TABLE1의 COL1 값이 14,15인 로우는 제외된다.

SELECT X.COL1,X.COL2,X.COL4,Y.COL3
FROM TABLE1 X,TABL2 Y
WHERE X.COL1=Y.COL1
     AND X.COL3='B'
     AND Y.COL2='2';

• 잘못된 OUTER 조인
  쿼리의 잘못을 수정한 아래 쿼리도 역시 COL1 값이 14,15인 로우는 제외된다.
  TABLE1의 COL1값이 14,15인 로우에 연결되는 TABLE2의 로우는 없으며 따라서 이 경우 Y.COL2의 값은 NULL이다.
  NULL값에 대하여 '2'로 이퀄연산자에 의한 비교를 하기 때문이다.

SELECT X.COL1,X.COL2,X.COL4,Y.COL3
FROM TABLE1 X,TABL2 Y
WHERE X.COL1=Y.COL1( + )
    AND X.COL3='B'
    AND Y.COL2='2'

4.2. OUTER 조인 실패의 해결

• 아래의 SQL에서 Y.COL2(+)='2'와 같이 OUTER 조인을 하면 Y의 모든 조건이 X의 집합과 OUTER 조인되어서 원하는 결과를 얻을 수 있다. 즉, Y.COL2(+)='2' 조건을 주지 않은 것과 동일한 결과를 얻을 수 있다.

SELECT X.COL1,X.COL2,X.COL4,Y.COL3
FROM TABLE1 X,TABL2 Y
WHERE X.COL1=Y.COL1( + )
    AND X.COL3='B'
    AND Y.COL2(+)='2';

• 아래의 SQL을 수행하면 Error가 발생한다. 이유는 OUTER 조인은 'IN'이나 'OR'을 사용하면 Error가 발생하기 때문이다.

SELECT X.COL1,X.COL2,X.COL4,Y.COL3
FROM TABLE1 X,TABL2 Y
WHERE X.COL1=Y.COL1( + )
    AND X.COL3='B'
    AND Y.COL2(+) IN ('1','2');

• 만일 Y.COL2='2'가 조건이 대입되어 OUTER 조인이 수행되지 않을 경우 OR Y.COL2 IS NULL을 조건을 대입하여 원하는 결과를 얻을 수 있다. 이유는 집합 Y에 연결되지 못한 Row의 컬럼값들이 모두 NULL이므로 이와 같이 NULL인 경우도 허용하도록 조건을 추가하여 문제를 해결한다.

SELECT X.COL1,X.COL2,X.COL4,Y.COL3
FROM TABLE1 X,TABL2 Y
WHERE X.COL1=Y.COL1( + )
    AND X.COL3='B'
    AND (Y.COL2='2' OR Y.COL2 IS NULL);

• 아래와 같이 View를 만들고 실행하면 IN을 사용한 것과 동일한 효과를 얻을 수 있다.

CREATE or REPLACE VIEW VIEW1 AS
 SELECT COL1, COL2, COL3
 FROM TABLE2
 WHERE COL2 IN ('1','2');

SELECT X.COL1, X.COL2, X.COL4, Y.COL3
FROM TABLE1 X, VIEW1 Y
WHERE X.COL1=Y.COL1(+) and X.COL2='B';

• 아래와 같이 인라인뷰를 생성하여도 동일한 결과를 추출할 수 있다.

SELECT X.COL1, X.COL2, X.COL4, Y.COL3
FROM TABLE1 X, (SELECT COL1, COL3
                FROM TABLE2
                WHERE COL2 IN ('1','2')) Y
WHERE X.COL1=Y.COL1(+) and X.COL2='B';

4.3. OUTER 조인의 실행계획

• 위의 그림에서 보는 것과 같이 TAB1이 먼저 Access 되어야 원하는 결과를 얻을 수 있다. 즉, OUTER 조인되는 집합이 나중에 수행되는 것이 유리한 경우에는 OUTER 조인을 하던, 하지 않든 전혀 상관이 없음을 의미한다.
• 반대로 OUTER 조인이 수행될 경우 그림에서 보여지는 것과 같이 TAB2에 없는 TAB1의 집합을 Access 하는 것이 논리적으로 불가능하다.
• 따라서 "혹시 데이타가 누락될지도 모른다"는 막연한 두려움때문에 OUTER조인을 사용해서는 안되고 반드시 필요한 경우만 사용해야 한다.

4.4. 하나 이상 집합과의 OUTER 조인

• D의 아웃조인 대상이 B,C로 두개이다. 이 SQL을 실행하면 Error가 발생한다. 그 이유는 어떤 집합이 OUTER 집합이 조인될 때 비교되는 상대집합은 반드시 하나가 되어야 하기 때문이다.

SELECT A.부서코드,MIN(A.부서명),C.자재코드,MIN(C.자재명),SUM(D.의뢰수량)
FROM 부서 A,구매의뢰 B,자재 C,구매의뢰자재내역 D
WHERE C.자재구분='소모품'    
    AND D.자재코드( + )=C.자재코드   
    AND A.위치='서울'
    AND B.부서코드=A.부서코드
     AND B.의뢰일자 BETWEEN '19980101' AND '19980131'
    AND D.부서코드( + )=B.부서코드
     AND D.일련번호( + )=B.일련번호           
GROUP BY A.부서코드,C.자재코드

• 아래와 같이 인라인뷰를 생성한 후 OUTER 조인을 실행하여 연결되는 상대값이 서로 다른 컬럼이었던 것을 이제 동일한 집합이 되었다. 이로 인하여 제한요소가 제거되었기 때문에 정상적으로 수행하게 된다.

SELECT X.부서코드,MIN(X.부서명),Y.자재코드,MIN(Y.자재명),SUM(X.의뢰수량)
FROM(
    SELECT A.부서코드,A.부서명,C.자재코드,C.의뢰수량
     FROM 부서 A,구매의뢰 B,구매의뢰자재내역 C
    WHERE B.부서코드=A.부서코드
        AND C.부서코드=B.부서코드
        AND C.일련번호=B.일련번호
        AND A.위치='서울'
       AND B.의뢰일자 BETWEEN '19980101' AND '19980131'
) X,자재 Y
WHERE Y.자재코드=X.자재코드( + )
    AND Y.자재구분='소모품'
GROUP BY X.부서코드,X.자재코드

About Doc.

• 최초작성자 : 안종식
• 최초작성일 : 2009년 5월 15일
• 이 문서는 오라클클럽 01.대용량 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 이화식님의 '대용량 데이터베이스 솔루션2'을 참고했습니다.*