h2.4.0 OUTER 조인 부가설명

h4.4.0.1 OUTER 조인이란?

1) 두개의 테이블 조인시 한쪽 테이블(기준테이블)의 로우에 대하여 다른 쪽 테이블(OUTER 테이블)에 일치하는 로우가 없을지라도 다른 쪽 테이블의 로우를 NULL로 하여 SELECT하는 조인 기법이다.
2) 단, OUTER 테이블에만 있고 기준테이블에는 없는 로우는 SELECT되지 않는다.
3) 하나의 테이불이 OUTER테이블이 되었다면 WHERE절에 사용된 그 테이블의 모든 칼럼에는 ( + ) 기호가 붙어야 한다.
4) 아래의 경우 TABLE1이 기준테이블이며 TABLE2가 OUTER테이블이다.

SELECT X.COL1,X.COL2,Y.COL3 ...
FROM TABLE1 X,TABLE2 Y
WHERE X.COL1=Y.COL1( + )

5) 하나의 테이블이 아웃조인의 대상으로 삼을 수 있는 테이블의 수는 한개뿐이다.

<가능>
B의 아웃조인 대상은 A이고 C의 아웃조인 대상은 A로서 각각 하나이다.

SELECT *
FROM TEMP A,TDEPT B,TCOM C
WHERE A.EMP_ID=B.BOSS_ID( + )
AND A.EMP_ID=C.EMP_ID( + )

<불가능(ORA-01417에러)>
B의 아웃조인 대상이 A이면서 B의 아웃조인 대상이 C이므로 아웃조인의 대상이 두개이다.

SELECT *
FROM TEMP A,TDEPT B,TCOM C
WHERE A.EMP_ID=B.BOSS_ID( + )
AND C.EMP_ID=B.BOSS_ID( + )

<가능/불가능?>

SELECT *
FROM TEMP A,TDEPT B,TCOM C
WHERE C.EMP_ID( + )=A.EMP_ID
AND B.DEPT_CODE( + )=A.DEPT_CODE

<가능/불가능?>

SELECT *
FROM TEMP A,TDEPT B,TCOM C
WHERE A.EMP_ID( + )=C.EMP_ID
AND A.DEPT_CODE( + )=B.DEPT_CODE

h4.4.0.2 상호 OUTER 조인

1) 오라클 9i 이전 버젼이나 FULL OUTER JOIN을 지원하지 않는 경우

     SELECT * 
    FROM T_EMP A,T_DEPT B
    WHERE A.DEPTCD( + )=B.DEPTCD
UNION --UNION ALL (X)
    SELECT * 
    FROM T_EMP A,T_DEPT B
    WHERE A.DEPTCD=B.DEPTCD( + )

2) 오라클 9i 이상이나 FULL OUTER JOIN을 지원하는 경우(ANSI QUERY)

SELECT *
FROM T_EMP A FULL OUTER JOIN T_DEPT B
ON (A.DEPTCD=B.DEPTCD)

3)결과

EMPNO	ENAME	DEPTCD	DEPTCD_1	DEPTNM
------------------------------------------
1111	KIM	10	10	영업부
2222	LEE	20	20	기획부
3333	PARK	30	30	자재부
4444	SONG	40		
			50	경영부

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

TABLE1,TABLE2에 대해 COL1을 조인키로하여 조인하되 TABLE1에서 COL3='B'인 조건을 만족하는 모든 로우를 구하라(3-32 페이지 참조)

h4.4.1.1 일반 조인
아래와 같은 일반조인은 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'

h4.4.1.2 잘못된 OUTER 조인
4.1.1 쿼리의 잘못을 수정한 아래 쿼리도 역시 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'

h2.4.2 OUTER 조인 실패의 해결

h4.4.1.2 옳바른 OUTER 조인
TABLE1의 COL1 값이 14,15인 로우도 SELECT 하고자 한다면 아래처럼 WHERE절에 사용된 ( + )기호가 붙은
테이블의 모든 칼럼에 대해 ( + ) 기호를 사용해 주어야 한다.

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'

h4.4.1.3 인라인뷰를 이용한 OUTER 조인
위의 쿼리에서 만일 조건이 AND Y.COL2( + ) IN('1','2')로 변한다면 OUTER조인시 에러가 발생한다.
OUTER조인은 IN,OR 연산자와 같이 사용할 수 없는 제약이 있기 때문이다.
이러한 제약을 인라인뷰를 이용하여 극복할 수 있다.

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.1.2의 쿼리문를 아래처럼 변형하면 된다.
TABLE1 테이블 로우에 매칭되는 로우는 Y.COL2='2' 조건에 의하여
매칭되지 않는 로우는 Y.COL2 IS NULL 조건에 의해 SELECT된다.

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)

h2.4.3 OUTER 조인의 실행계획

• OUTER 조인 상황이 되면 엑세스 순서는 무조건 기준테이블 -> OUTER 테이블 순서가 된다.
• 위의 예에서 든 TABLE1 과 TABLE2를 볼 때 만일 엑세스 순서가 TABLE2 -> TABLE1로 된다면 TABLE2에 없는 TABLE1의 로우를 찾는 것으로
  이는 논리적으로 불가능하다.
• 따라서 "혹시 데이타가 누락될지도 모른다"는 막연한 두려움때문에 OUTER조인을 사용해서는 안되고 반드시 필요한 경우만 사용해야 한다.

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

h4.4.4.1 예시 테이블(식별자관계-3-41페이지 참조)
1)부서:#부서코드,부서명,위치
2)구매의뢰:#부서코드,#일련번호,의뢰일자,출고희망일,승인일자
3)자재:#자재코드,자재명,자재구분,규격
4)구매의뢰자재내역:#부서코드,#일련번호,#자재코드,의뢰수량,승인수량,미출고잔량,구매단가

부서:구매의뢰=1:M
구매의뢰:구매의뢰 자재내역=1:M
자재:구매의뢰 자재내역=1:M

h4.4.4.2 문제제시
부서위치가 '서울'인 부서들에 대해 자재구분인 '소모품'인 자재들의 1998년 1월 한달간의 의뢰내역의 집계를 구하라. 단, 구매의뢰되지 않은 자재도 모두 보여라.

h4.4.4.3 쿼리문
1)잘못된 쿼리문 : D 의 아웃조인 대상이 B,C로 두개이다.

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

2)옳바른 쿼리문

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

문서에 대하여

• 최초작성자 : 김강환
• 최초작성일 : 2007년 12월 08일
• 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
• 이 문서의 내용은 이화식님의 대용량 데이터베이스 솔루션2 를 참고했습니다.