Joins that combine outer joins

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

customer_num lname             order_num stock_num manu_code quantity

         114 Albertson
         118 Baxter
         113 Beatty
         103 Currie
         115 Grant                  1010
         ⋮
         117 Sipes                  1012
         117 Sipes                  1007
         105 Vector
         121 Wallack                1018       302 KAR              3
         106 Watson                 1014
         106 Watson                 1004

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.