sel_ojoin3.sql

The following example SELECT statement is the third type of outer join, known as a nested outer join. It queries on table data by creating an outer join, which is the result of an outer join to a third table.

This 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 an outer join, which combines this information with data from the dominant customer table. This query preserves order numbers that the previous example eliminated, returning rows for orders that do not contain items with either manufacturer code. An optional ORDER BY clause reorganizes the data.

SELECT c.customer_num, 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;