OUTER JOIN 에 대하여
====================

Purpose
-------
Outer join의 효과과 이용방법에 대해 이해한다.


Explanation
-----------

1. 개념

다음의 용어에 대해 우선 살펴보자 :

1) outer-join column - symbol(+) 을 사용하는 column 이다 .
예를 들어 EMPNO(+) ,DEPT.DEPTNO(+) 는 outer join column들이다.

2) simple predicate - AND , OR,NOT 을 가지지 않는 단순한 관계표현으로
A=B 의 관계로 표현된다.

3) outer join predicate - 한개 이상의 outer join column 을 갖는 simple
predicate 이다.


2. OUTER JOIN 사용법 - RULES

outer join predicate 는 오직 1 table 의 column 들 만이 outer join
column 으로 이용되어져야 한다. 즉 한 single outer join predicate 의
모든 outer join column 은 모두 같은 table이어야 한다.
이런 취지에서 다음 statement 는 틀린 것이다.

EMP.EMPNO(+) = DEPT.DEPTNO(+)

이것은 두 table 의 outer join column 들이다.

한 predicate 의 한 column 이 outer join column 이면 같은 table 의 모든
column 은 outer join column 이어야 한다.

이 취지에서 다음 문장은 틀린 것이다.

EMP.SAL + EMP.COMM(+) = SALGRADE.HIGH

한 table 의 column 들이 outer join 것과 아닌 것과 outer join 인것으로
섞여있기 때문이다.

predicate 에서 (+) 표시가 붙은 table 은 다른 table 을 direct 하게
outer join 한다. indirect 하게 다른 tabe 을 outer join 한다는 것은
그들 table 자체가 또 outer join 하는 경우이다.
이 경우 한 table 은 direct하게든 indeirect 하게든 자기 자신에게 outer
join 하는 경우는 허용되지 않는다.

다음의 문장은 이런 취지에서 틀린 경우이다.

EMP.EMPNO(+) = PERS.EMPNO
AND PERS.DEPTNO(+) = DEPT.DEPTNO
AND DEPT.JOB(+) = EMP.JOB - circular outer
join relationship

3. OUTER JOIN 실행

주어진 table T 에는 outer join 과 non-outer join 이 있다.
실행시 다음처럼 수행된다.

1) The result of joining all tables mentioned in table T's
outer join predicates is formed ( by recursive application
of this algorithm ).

2) For each row of the result, a set of composite rows is
formed, each consisting of the original row in the
result joined to a row in table T for which the composite
row satisfies all of table T's outer join predicates.

3) If a set of composite rows is the null set, a composite
row is created consisting of the original row in the
result joined to a row similar to those in table T, but
with all values set to null.

4) Rows that do not pass the non-outer join predicates are removed.

This may be summarised as follows. Outer join
predicates ( those with (+) after a column of table T ), are
evaluated BEFORE table T is augmented with a null row. The null
row is added only if there are NO rows in table T that satisfy
the outer join predicates. Non-outer join predicates are
evaluated AFTER table T is augmented with a null row (if needed)


4. OUTER JOIN - RECOMMENDATIONS

Certain types of outer joins in complicated logical
expressions may not be well formed. In general, outer join
columns in predicates that are branches of an OR should be
avoided. Inconsistancies between the branches of the OR can
result in an ambiguous query, and this may not be detected. It
is best to confine outer join columns to the top level of the
'where' clause, or to nested AND's only.


5. OUTER JOIN - ILLUSTRATIVE EXAMPLES

1) Simple Outer Join

SELECT ENAME, LOC
FROM DEPT, EMP
WHERE DEPT.DEPTNO = EMP.DEPTNO(+)

The predicate is evaluated BEFORE null augmentation. If
there is a DEPT row for which there are no EMP rows, then a null
EMP row is concatenated to the DEPT row.


2) Outer Join With Simple Post-Join Predicates

SELECT ENAME, LOC
FROM DEPT, EMP
WHERE DEPT.DEPTNO = EMP.DEPTNO(+)
AND EMP.DEPTNO IS NULL

The second simple predicate is avaluated AFTER null
augmentation, since there is no (+), removing rows which were
not the result of null augmentation and hence leaving only DEPT
rows for which there was no corresponding EMP row.


3) Outer Join With Additional Pre-Join Predicates

SELECT ENAME, LOC
FROM DEPT, EMP
WHERE DEPT.DEPTNO = EMP.DEPTNO(+)
AND 'CLERK' = EMP.JOB(+)
AND EMP.DEPTNO IS NULL

The predicate on EMP.JOB is evaluated at the same time
as the one on EMP.DEPTNO - before null augmentation. As a
result, a null row is augmented to any DEPT row for which there
are no corresponding clerks's in the EMP table. Therefore, this
query displays departments containing no clerks.

Note that it the (+) were omitted from the EMP.JOB
predicate, no rows would be returned. In this case, both the
EMP.JOB and EMP.DEPTNO IS NULL predicates are evaluated AFETR
the outer join, and there can be no rows for which both are
true.

출처 : otn

=오라클 유지보수 유건데이타