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
'outer-join column' - a column reference followed by the symbol
'simple predicate' - a logical expression containing no
'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).
EMP.EMPNO(+) = DEPT.DEPTNO(+) - outer join columns from
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.
EMP.EMPNO(+) = PERS.EMPNO
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
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
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
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.
Have a Oracle Question
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.