To achieve multiple levels of nesting, you can create
a join that employs any combination of the three types of outer joins.
Using the ANSI syntax, the following query creates a join that is
the result of a combination of a simple outer join on two tables and
a second outer join. Figure 1: Query
SELECT c.customer_num, c.lname, o.order_num,
stock_num, manu_code, quantity
FROM customer c, OUTER (orders o, OUTER items i)
WHERE c.customer_num = o.customer_num
AND o.order_num = i.order_num
AND manu_code IN ('KAR', 'SHM')
ORDER BY lname;
The query first performs an outer join on the orders and items tables,
retrieving information on all orders for items with a manu_code of
KAR or SHM. It then performs a second outer join that combines this
information with data from the dominant customer table.Figure 2: Query result
You can specify the join conditions in two ways when
you apply an outer join to the result of an outer join to a third
table. The two subservient tables are joined, but you can join the
dominant table to either subservient table without affecting the results
if the dominant table and the subservient table share a common column.