SAND CDBMS SQL Reference Guide
Joins

Nested Joins

Joins can be nested within other joins to produce somewhat more complex queries. The order of multiple joins can affect the output of the query, especially in the case of outer joins. When nested, the innermost join statements are processed first. Putting nested join statements within parentheses is not required but recommended; this will enhance the readability of the overall SQL statement and help prevent unintended join ordering.

Joins can be nested in a manner similar to the following (where letters indicate tables and the join clauses with the greatest indentation are processed first):

A JOIN
  B JOIN
    C JOIN D
    ON join-condition-CD
  ON join-condition-BCD
ON join-condition-ABCD

In this case, C JOIN D ON join-condition-CD is performed first. Next, B is joined with the intermediate result table of the first join ON join-condition-BCD. Finally, A is joined with the output of the second join ON join-condition-ABCD.

Another possible nested join structure is the following:

  A JOIN B ON join-condition-AB
JOIN
  C JOIN D ON join-condition-CD
ON join-condition-ABCD

Here, both A JOIN B and C JOIN D are processed first (the order does not matter, since these two join clauses are nested at the same level). The intermediate result tables from those joins are then joined themselves ON join-condition-ABCD.

Suppose we wanted to join the result of the previous sample query with the Subpart table, which includes a column named subpart (containing data similar to the pno columns in the Part and Partsupp tables). The Subpart table contains several rows with part number (subpart column) values that do not appear in either the Part or Partsupp tables.

The following sample query joins the Subpart table with the result of the outer join of the Part and Partsupp table (three-way join), displaying the non-matching part number values from Subpart in the result:

SELECT part.pno, subpart.product
FROM subpart LEFT JOIN
(part FULL JOIN partsupp
ON part.pno = partsupp.pno)
ON subpart.subpart = part.pno;

The same query without parentheses is processed the same way, but it is easier to see with the parentheses that the Subpart table is left joined with the table derived from the nested join (part FULL JOIN partsupp ON part.pno = partsupp.pno).