Nested ANSI Outer Joins

Nested outer joins use the result set of one outer join as the table reference for another.

For example:

select t.title_id, title, ord_num, sd.stor_id, stor_name
from (salesdetail sd
left join titles t
on sd.title_id = t.title_id)   /*join #1*/
left join stores 
on sd.stor_id = stores.stor_id /*join #2*/

title_id  title         ord_num  stor_id  stor_name
--------  ------------  -------  -------  ------------------------
TC3218    Onions, L...  234518   7896     Fricative Bookshop
TC7777    Sushi, An...  234518   7896     Fricative Bookshop
. . .
TC4203    Fifty Yea...  234518   6380     Eric the Read Books        
MC3021  The Gourmet...  234518   6380     Eric the Read Books        
(116 rows affected)

In this example, the joined table between the salesdetail and titles tables is logically produced first and is then joined with the columns of the stores table where salesdetail.stor_id equals stores.stor_id. Semantically, each level of nesting in a join creates a joined table and is then used for the next join.

In the query above, because the first outer join becomes an operator of the second outer join, this query is a left-nested outer join.

This example shows a right-nested outer join:

select stor_name, qty, date, sd.ord_num
from salesdetail sd left join (stores /*join #1 */
left join sales on stores.stor_id = sales.stor_id) /*join #2 */
on stores.stor_id = sd.stor_id
where date > "1/1/1990"

stor_name      qty  date                 ord_num
------------   ---- ------------------   --------------------
News & Brews   200  Jun 13 1990 12:00AM  NB-3.142
News & Brews   250  Jun 13 1990 12:00AM  NB-3.142
News & Brews   345  Jun 13 1990 12:00AM  NB-3.142
. . .
Thoreau Read   1005 Mar 21 1991 12:00AM  ZZ-999-ZZZ-999-0A0
Thoreau Read   2500 Mar 21 1991 12:00AM  AB-123-DEF-425-1Z3
Thoreau Read   4000 Mar 21 1991 12:00AM  AB-123-DEF-425-1Z3

In this example, the second join (between the stores and the salestables) is logically produced first, and is joined with the salesdetailtable. Because the second outer join is used as the table reference for the first outer join, this query is a right-nested outer join.

If the on clause for the first outer join (“from salesdetail. . .”) fails, it supplies null values to both the stores and salestables in the second outer join.

Parentheses in Nested Outer Joins

Nested outer joins produce the same result set with or without parenthesis. Large queries with many outer joins can be much more readable for users if the joins are structured using parentheses.

The on Clause in Nested Outer Joins

The placement of the on clause in a nested outer join determines which join is logically processed first. Reading from left to right, the first on clause is the first join to be defined.

In this example, the position of the on clause in the first join (in parentheses) indicates that it is the table reference for the second join, so it is defined first, producing the table reference to be joined with the authors table:

select title, price, au_fname, au_lname
from (titles left join titleauthor 
on titles.title_id = titleauthor.title_id)   /*join #1*/
left join authors 
on titleauthor.au_id = authors.au_id      /*join #2*/
and titles.price > $15.00

title            price      au_fname      au_lname
---------------  ---------  ------------  -------------
The Busy Exe...  19.99      Marjorie      Green
The Busy Exe...  19.99      Abrahame      Bennet
. . .
Sushi, Anyon...  14.99      Burt          Gringlesby
Sushi, Anyon...  14.99      Akiko         Yokomoto 
(26 rows affected)

However, if the on clauses are in different locations, the joins are evaluated in a different sequence, but still produce the same result set (this example is for explanatory purposes only; if joined tables are logically produced in a different order, it is unlikely that they will produce the same result set):

select title, price, au_fname, au_lname
from titles left join
(titleauthor left join authors
on titleauthor.au_id = authors.au_id)        /*join #2*/
on titles.title_id = titleauthor.title_id    /*join #1*/ 
and au_lname like"Yokomoto"

title                    price        au_fname          au_lname 
----------------------   -------      -----------       -----------            
The Busy Executive’s     19.99        Marjorie          Green 
The Busy Executive’s     19.99        Abraham           Bennet
. . .
Sushi, Anyone?           14.99        Burt              Gringlesby
Sushi, Anyone?           14.99        Akiko             Yokomoto

(26 rows affected)

The position of the on clause of the first join (the last line of the query) indicates that the second left join is a table reference of the first join, so it is performed first. That is, the result of the second left join is joined with the titles table.