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 sales tables) is logically produced first, and is joined with the salesdetail table. 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 sales tables in the second outer join.
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 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.
