Outer join for a simple join to a third table

Using the HCL Informix® syntax, the following query shows an outer join that is the result of a simple join to a third table. This second type of outer join is known as a nested simple join.

Figure 1. Query

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;

The 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 to combine this information with data from the dominant customer table. An optional ORDER BY clause reorganizes the data into the following form.

Figure 2. Query result

customer_num lname             order_num stock_num manu_code quantity

         114 Albertson
         118 Baxter
         113 Beatty
         ⋮
         105 Vector
         121 Wallack                1018       302 KAR              3
         106 Watson