Oracle Outer Join Tips Oracle Database Tips by Donald Burleson

An Oracle SQL outer join differs from a natural join because it includes non-matching rows.  Oracle SQL has several joins syntax variations for outer joins.

Outer join (+) syntax examples

The most common notation for an outer join is the (+) notation.  This, from the great book "Easy Oracle SQL" by Lt. Col. John Garmany:

For example, if I list my authors and the books they have written, I get the results below.

The problem with this listing is that there are ten authors in the author table and only eight listed.  The remaining two authors have not yet written a book. What if I wanted the listing to include these two authors?  Because they do not match the equal join, I will need to use an outer join.  An outer join will include all rows from the outer table and those matching rows from the other table.  Rows that are included from the outer table that have no match in the other table will have NULLs in those columns.

In the example above, the AUTHOR table is on the left, and we are using a left outer join, so we get all the rows in the AUTHOR table and the matching rows in the book_author table.  Notice that both authors clark and mee now are listed, and the book_key column is NULL.  In the standard Oracle format, outer joins can be confusing.  Below is the same query in the standard Oracle format.

Notice the (+) in the WHERE clause.  This indicates a left outer join.  If we were using a right outer join, the WHERE clause would be:

author.author_key(+) = book_author.author_key

Here, all the rows from the book_author table would be included and the missing rows from the AUTHOR table would be NULL.  The example below demonstrates the ANSI right outer join.

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.