시소당
일반적으로 OUTER JOIN이란 두 개 이상의 테이블을 JOIN할 경우에 테이블 중에서 기준이 되는 테이블의 데이터가 다른 테이블의 데이터와 일치 되지 않는 경우에도 JOIN을 할 수 있도록 하기 위해서 사용합니다. OUTER JOIN을 일반적인 JOIN문으로 표현하면, SQL문이 길어지며 또한 실행계획이 인덱스를 이용하지 못하여 수행시간이 늘어납니다. 이러한, 경우에 OUTER JOIN으로 간단하게 표현할 수 있으며, 수행시간 또한 일반적인 JOIN과 거의 비슷합니다.
코드 마스터 성격을 가진 테이블 TAB1과 이를 사용하는 테이블 TAB2를 조인하여 모든 코드 값에 대해 해당되는 TAB2의 데이터를 표시하도록 하고 싶은 경우입니다. 이때 TAB1의 모든 값에 대해 TAB2의 데이터가 반드시 존재한다는 보장이 없으므로 OUTER 조인을 사용하여 해결합니다. 위와 같이 하면 원하는 결과 중에 테이블 TAB1의 컬럼 KEY1의 값이 'D'이거나 'E'인 로우 즉, 테이블 TAB2의 COL1값이 NULL인 로우가 나오지 않게 됩니다. 그래서 다음과 같은 SQL문을 사용하여 원하는 결과를 도출합니다. OUTER JOIN의 사용 방법은 매우 간단합니다.
JOIN된 테이블의 컬럼에 (+)를 사용하여 OUTER JOIN을 사용합니다.
단 이때 조건절에서 어느 쪽에 (+) 가 붙어야 하는지 신중하게 생각하셔야 합니다.
다음과 같이 기준이 되는 테이블이 TAB1이고 JOIN된 테이블이 TAB2일 경우에 테이블 TAB1의 컬럼 KEY1에 대해서 모든 값이 결과 값으로 추출하고자 할 경우에 JOIN된 테이블 TAB2 컬럼에 대해서 (+)를 사용합니다. 즉, 데이터가 부족한 쪽에 (+)를 붙이시면 됩니다.
SELECT A.KEY1, A.FLD1, A.FLD2, B.KEY2, B.COL1, B.COL2
FROM TAB1 A, TAB2 B
WHERE A.KEY1 = B.KEY2(+) ;
위 OUTER JOIN에서 테이블 TAB1에 있는 로우들이 테이블 TAB2에 없는 경우에 일반적인 JOIN으로는 로우들을 추출할 수 없으나 위 SQL문과 같이 OUTER JOIN을 사용한 경우에는 테이블 TAB2에 없는 모든 로우들을 추출할 수 있습니다.
예)
SQL> SELECT B.EMPNO, B.ENAME, A.DEPTNO, A.DNAME
2 FROM DEPT A, EMP B
3 WHERE A.DEPTNO = B.DEPTNO
4 AND B.EMPNO > 7700
5 ORDER BY B.EMPNO, B.ENAME, A.DEPTNO, A.DNAME
테이블명 : 인덱스명 : 컬럼구성
DEPT A : DEPT_PK : DEPTNO
EMP B : EMP_PK : EMPNO
EMP B : EMP_IX1 : DEPTNO
EMPNO ENAME DEPTNO DNAME
---------- -------------------- ---------- --------------------
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
8 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
4 3 INDEX (RANGE SCAN) OF 'EMP_PK' (UNIQUE)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
6 5 INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)
앞서 살펴본 SQL문의 결과에 테이블 DEPT의 컬럼 DEPTNO에 대해서 테이블 EMP의 컬럼 DEPTNO가 매칭이 되는 것이 없는 로우에 대해서도 컬럼 EMPNO, ENAME를 조회하고자 할 경우에 어떻게 해야 할까요?
SQL> SELECT B.EMPNO, B.ENAME, A.DEPTNO, A.DNAME
2 FROM DEPT A, EMP B
3 WHERE A.DEPTNO = B.DEPTNO
4 AND B.EMPNO > 7700
5 AND ( A.DEPTNO IS NOT NULL OR A.DEPTNO IS NULL )
6 ORDER BY B.EMPNO, B.ENAME, A.DEPTNO, A.DNAME
SQL문에 ( A.DEPTNO IS NOT NULL OR A.DEPTNO IS NULL )를 추가해서 SQL문을 실행하였습니다. 하지만 결과는 앞에서 살펴본 일반적인 JOIN 문과 같은 결과가 나왔습니다. 이와 같은 이유는 A.DEPTNO = B.DEPTNO에서 B.DEPTNO IS NULL인 로우들은 해당되지 않기 때문입니다. SQL문에 ( A.DEPTNO IS NOT NULL OR A.DEPTNO IS NULL )를 추가해서는 원하는 결과를 얻을 수 없는 것을 확인하였습니다.
여기에서 A.DEPTNO가 NULL인 경우 즉 B.DEPTNO의 컬럼에는 존재하나 A.DEPTNO의 컬럼에는 존재하지 않는 로우들을 추출하고자 할 경우에 바로 OUTER JOIN을 사용합니다.
예)
SQL> SELECT B.EMPNO, B.ENAME, A.DEPTNO, A.DNAME
2 FROM DEPT A, EMP B
3 WHERE A.DEPTNO(+) = B.DEPTNO
4 AND B.EMPNO > 7700
5 ORDER BY B.EMPNO, B.ENAME, A.DEPTNO, A.DNAME
6 /
EMPNO ENAME DEPTNO DNAME
---------- -------------------- ---------- ----------------------------
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
8000 NEW FORD
9 rows selected.
SQL>
앞에서 살펴본 SQL 문은 OUTER JOIN 기준이 되는 테이블이 EMP인 경우입니다.
그런데 OUTER JOIN의 기준이 되는 테이블이 만약 DEPT 라면, 즉 추출하고자 하는 내용이 테이블 DEPT의 로우는 모두 보여주고 EMP테이블의 로우는 테이블 DEPT와 JOIN되면 보여주고 그렇지 않은 경우는 NULL로 나타내고자 한다면 어떻게 해야 할까요? 기준이 되는 테이블이 테이블 DEPT인 OUTER JOIN으로 표현하면 위 내용을 SQL문으로 처리할 수가 있습니다. 즉 OUTER JOIN이 되는 테이블 EMP의 WHERE조건절에 모든 컬럼에 대해서 (+)기호를 붙이는 것입니다. 그럼 수정된 SQL문을 보겠습니다.
SQL> SELECT B.EMPNO, B.ENAME, A.DEPTNO, A.DNAME
2 FROM DEPT A, EMP B
3 WHERE A.DEPTNO = B.DEPTNO(+)
4 AND B.EMPNO(+) > 7700
6 ORDER BY B.EMPNO, B.ENAME, A.DEPTNO, A.DNAME
OUTER JOIN의 기준이 되는 테이블이 DEPT이고 OUTER JOIN테이블이 EMP일 경우는 위 SQL문 처럼 WHERE조건절에 있는 테이블 EMP의 모든 컬럼에는 (+)표시를 추가해야 됩니다. 비록 비교 값이 컬럼이 아닌 상수라 할지라도 일관되게 (+)를 모두 붙여 주어야 합니다. 만약에 상수와 비교되는 컬럼에 (+)를 붙이지 않았는데도 불구하고 원하는 결과 값을 얻었을 경우에는 OUTER JOIN을 할 필요가 없는 데도 불구하고 OUTER JOIN을 했다는 것입니다.
결론적으로 OUTER JOIN시에 OUTER JOIN되는 테이블의 WHERE조건절에 있는 모든 컬럼은 반드시 (+)기호를 붙여야 합니다. 지금 까지는 OUTER JOIN의 정확한 사용법에 대해서 학습하셨습니다. 그런데 OUT JOIN을 정확하게 사용하는 경우에는 문제가 발생하는 경우가 있습니다. 이런 경우에는 어떻게 OUTER JOIN을 사용하는지 알아보도록 하겠습니다.
OUTER JOIN된 테이블의 WHERE조건문에 있는 컬럼의 비교 연산자가 IN, BETWEEN, LIKE, OR 인 경우에 (+)표시를 해서 사용했더니 구문 오류가 발생합니다. 즉 OUTER JOIN에서는 연산자 IN, BETWEEN, LIKE, OR를 사용할 수 없다는 에러메시지 였습니다. 그러면 연산자가 IN, BETWEEN, LIKE , OR일 경우에는 OUTER JOIN을 사용할 수 없을까요? 다음에서 연산자가 IN, BETWEEN, LIKE, OR등일 경우에 OUTER JOIN을 사용하는 방법에 대해서 알아보도록 하겠습니다.
예)
SQL> SELECT B.EMPNO, B.ENAME, A.DEPTNO, A.DNAME
2 FROM DEPT A, EMP B
3 WHERE A.DEPTNO = B.DEPTNO
4 AND B.EMPNO > 7700
5 AND A.LOC IN ( 'CHICAGO', 'BOSTON')
6 ORDER BY B.EMPNO, B.ENAME, A.DEPTNO, A.DNAME
7 /
EMPNO ENAME DEPTNO DNAME
---------- -------------------- ---------- --------------------
7844 TURNER 30 SALES
7900 JAMES 30 SALES
2 rows selected.
SQL>
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
4 3 INDEX (RANGE SCAN) OF 'EMP_PK' (UNIQUE)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
6 5 INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)
위 SQL문에서 테이블 EMP에 대하여 테이블 DEPT를 OUTER JOIN하는 경우에 대하여 알아보도록 하겠습니다. OUTER JOIN되는 테이블이 DEPT이므로 테이블 DEPT의 컬럼에는 모두 (+)를 사용해야 합니다. 앞서 말씀 드렸듯이 테이블 EMP의 로우중 EMPNO > 77인 것들을 모두 추출하고자 합니다. 그러면 다음과 같이 OUTER JOIN을 사용하기 위해서 OUTER JOIN된 테이블 DEPT의 WHERE조건문에 쓰인 모든 컬럼에 대해서 (+)기호를 사용했습니다.
예)
SQL> SELECT B.EMPNO, B.ENAME, A.DEPTNO, A.DNAME
2 FROM DEPT A, EMP B
3 WHERE A.DEPTNO(+) = B.DEPTNO
4 AND B.EMPNO > 7700
5 AND A.LOC(+) IN ( 'CHICAGO', 'BOSTON')
6 ORDER BY B.EMPNO, B.ENAME, A.DEPTNO, A.DNAME
OUTER JOIN에서 IN을 사용 했더니 오라클 구문에러인 ora-01719 error가 발생했습니다. ora-01719는 outer join의 표시인 (+)를 적용한 컬럼들이 Where문에서 IN, OR, LIKE,BETWEEN를 연산자로 사용할 경우에 발생합니다. 그러면 원하는 결과를 얻기 위해서, 위의 SQL 문을 어떻게 수정하여야 할까요?
예) --> 이렇게 수정해봄
SQL>
1 SELECT B.EMPNO, B.ENAME, A.DEPTNO, A.DNAME
2 FROM DEPT A, EMP B
3 WHERE A.DEPTNO(+) = B.DEPTNO
4 AND B.EMPNO > 7700
5 AND A.LOC(+) = 'CHICAGO'
6 UNION ALL
7 SELECT B.EMPNO, B.ENAME, A.DEPTNO, A.DNAME
8 FROM DEPT A, EMP B
9 WHERE A.DEPTNO(+) = B.DEPTNO
10 AND B.EMPNO > 7700
11 AND A.LOC(+) = 'BOSTON'
12* ORDER BY EMPNO, ENAME, DEPTNO, DNAME
SQL>
위에서 실행결과를 보면 Union all의 결과로 중복된 행이 있습니다. 중복을 제거하도록 UNION ALL대신에 UNION을 사용한다고 하더라도 결과 중 DEPTNO가 7844, 7900인 행으로 인하여 우리가 원하는 아래의 결과는 나올 수 없는 것을 알 수 있습니다.
SQL> /
EMPNO ENAME DEPTNO DNAME
---------- -------------------- ---------- ----------------------------
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER 30 SALES
7876 ADAMS
7900 JAMES 30 SALES
7902 FORD
7934 MILLER
8000 NEW FORD
9 rows selected.
OUTER JOIN에서 IN, OR등의 연산자를 사용할 수 없어서 UNION ALL또는 UNION으로 SQL문을 수정하였으나 우리가 원하는 결과는 얻지 못했습니다.
그러면 인라인뷰(IN-LINE VIEW)를 이용하여 해결해 보도록 하겠습니다. 인라인 뷰란 FROM절에서 먼저 SQL문의 조건을 사용하는 것으로
일반적으로 인라인 뷰가 먼저 실행됩니다.
예) --> 최종으로 이렇게 수정
1 SELECT B.EMPNO, B.ENAME, A.DEPTNO, A.DNAME
2 FROM (SELECT DEPTNO, DNAME, LOC
3 FROM DEPT
4 WHERE LOC IN ('CHICAGO','BOSTON')) A, EMP B
5 WHERE A.DEPTNO(+) = B.DEPTNO
6 AND B.EMPNO > 7700
7* ORDER BY EMPNO, ENAME, DEPTNO, DNAME
SQL> /
EMPNO ENAME DEPTNO DNAME
---------- -------------------- ---------- ----------------------------
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER 30 SALES
7876 ADAMS
7900 JAMES 30 SALES
7902 FORD
7934 MILLER
8000 NEW FORD
9 rows selected.