ORACLE OUTER JOIN #1
from
1.
ORACLE
OUTER JOIN #1
OUTER JOIN 의 이상한 케이스에 대한 잡담이다.
우선 emp table 에
deptno 이 null 인 데이터를 하나 입력한다.
SQL> insert into emp(empno,deptno)
values(8000,null);
1 row created.
SQL> commit;
Commit complete.
흔히 아는 equi 조건을 사용했다. 당연히도 deptno 가 null
인 값은 제외된다. null 과 상수를 비교하면 결과는
unknown 이 되며 해당 row 는 결과에서 제외된다.
SQL> ed
Wrote file afiedt.buf
1 select empno from emp,dept
2*
where emp.deptno = dept.deptno
SQL> /
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
EMPNO
----------
7900
7902
7934
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select empno from emp,dept
2 where emp.deptno = dept.deptno
3*
and dept.deptno is null
SQL> /
no rows selected
앞서 수행했던 동일 쿼리에 대해 is null 조건을 추가했다. 쿼리를 보고 이야기 하면 값의 비교가 가능한 row 자체가 제외된다.
operation 으로 이야기 하면 아래와 같이도 이야기 할 수 있다.
<driving table 이 emp>
emp 를 엑세스
해 상수값을 얻었으나 비교할 값이 null 이라 값을 비교할 수 없다.
<driving table 이 dept>
dept 를 엑세스
해 값을 얻었으나 연결고리가 null 이라 값을 비교할 수 없다.
(unknown)
SQL> ed
Wrote file afiedt.buf
1 select empno from emp,dept
2 where emp.deptno =
dept.deptno(+)
3*
and dept.deptno is null
앞의 쿼리를 불완전한 outer join 으로 바꾼다.
제대로 된 outer join 으로 만들려면 조건절에도 (+) 를 표기해야 한다.
쿼리만을 보고 이야기를 하면 emp 를 먼저 driving 한 후 dept 와 조인을 시도하는데
<INNER TABLE 이 0건>
EMP 의 모든 ROWS 가 표현된다.
<INNER TABLE 이 0건이 아닐 때>
위 쿼리에서 0건이 아니라는 것은 연결고리가 NULL 인 데이터가 있다는 것이다.
-NULL 만
있는 경우
EMP 의 모든 ROWS 가 표현된다.
-NULL 외에
연결을 만족하는 값이 있을 때
연결고리를 만족하는 ROW 가 제외되며 연결고리를 확인할
수 없는 EMP 의 ROW 가 표현된다.
때문에 EMP 의
DEPTNO 가 DEPT 의 DEPTNO 에
존재하는 ROW 는 모두 제거된다.
SQL> /
EMPNO
----------
8000
위 결과는 ANTI JOIN 의 결과와 동일하다. 이 쿼리에 대한 plan 은 아래와 같다.
SQL> select * from
table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 3496123964
------------------------------------------------------------------------------
| Id
| Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0
| SELECT STATEMENT | |
1 | 10 | 3
(0)| 00:00:01 |
| 1
| NESTED LOOPS ANTI | |
1 | 10 | 3
(0)| 00:00:01 |
| 2
| TABLE ACCESS FULL| EMP |
14 | 98 | 3
(0)| 00:00:01 |
|* 3
| INDEX UNIQUE SCAN| PK_DEPT | 4 |
12 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by
operation id):
---------------------------------------------------
3 -
access("EMP"."DEPTNO"="DEPT"."DEPTNO")
15 rows selected.
플랜을 보면 실제로도 ANTI JOIN 이 일어났음을 알
수 있다.
지금까지 본 내용을 정리하면 지금까지 수행해온 SQL 은 OUTER JOIN 과는 다르다. QUERY 를 보고 이야기 하면
조인에 성공한 값들은 제외된다. 이 내용은 ANTI JOIN 과
일치한다.
또 OUTER JOIN 의 다른 면에 대해 볼 수 있었다. 전에 생각해왔던 OUTER JOIN 은 DRIVING TABLE 에서 얻어진 결과가 조인에 참가하는데 이는 조인의 성공/실패 여부를 떠나 참여했던 모든 ROW 결과를 얻을 수 있다고 생각했지만
그렇지 않은 경우도 있다는 것을 보았다.