시소당
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.자재코드