The sel_ojoin2.sql command file

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 about 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;