The purpose of this technical note is to provide some guidelines on how to use the outer join facility provided by ORACLE.
1.1 Outer Join Semantics - Definitions
The following terms, used to describe the operation on outer joins, are defined :-
'outer-join column' - a column reference followed
by the symbol
(+), e.g. EMPNO(+) and DEPT.DEPTNO(+)
are outer join columns
'simple predicate' - a logical expression containing
no AND's,
OR's, or NOT's (usually a simple
relation such as A = B )
'outer join predicate' - a simple predicate containing one or more outer join columns.
2. OUTER JOIN SYNTAX - RULES
An outer join predicate may only
contain outer join columns from one table ( in other words,
all outer join columns in a single outer join predicate must
belong to the same table).
This means, for example, that the following statement
is illegal :-
EMP.EMPNO(+) = DEPT.DEPTNO(+) - outer join
columns from two tables
Also, if a column in a predicate
is an outer join column, then all columns
from the same table must be outer join columns in that predicate.
This means, for example, that the following statement is illegal :-
EMP.SAL + EMP.COMM(+) = SALGRADE.HIGH - mixed columns
from one table. In a predicate, the table referenced with a (+) is
directly 'outer joined' to all other tables in
the predicate.
It is indirectly 'outer joined' to any tables
to which these other tables are themselves 'outer joined'. A predicate
may not be directly or indirectly 'outer joined' to itself.
This means, for example, that the following
combination of predicates is illegal :-
EMP.EMPNO(+) = PERS.EMPNO
AND PERS.DEPTNO(+) = DEPT.DEPTNO
AND DEPT.JOB(+) = EMP.JOB
- circular outer join relationship
3. OUTER JOIN EXECUTION
For a given table, T, there may be both outer join and non-outer join predicates. Execution occurs (conceptually ) as follows :-
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. Inconsistencies 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
5.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.
5.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 evaluated 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.
5.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 AFTER the outer join, and there can be no rows for which both are true.
