Guidelines For Using The Outer Join Syntax

1.  INTRODUCTION

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.

See Also
What Does The Keep Buffer Pool Do

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.