sel_ojoin2.sql

The following example command file creates an outer join, which is the result of a simple join to a third table. This second type of outer join is called a nested simple join.

This query first performs a simple 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. An optional ORDER BY clause reorganizes the data.

SELECT c.customer_num, c.lname, o.order_num, 
     i.stock_num, i.manu_code, i.quantity
   FROM customer c, OUTER (orders o, 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;