Using a Join as the Dominant or Subordinate Part of an Outer Join

With the ANSI join syntax, you can nest joins. You can use a join as the dominant or subordinate part of an outer or inner join.

Suppose you want to modify the previous query (the post-join filter example) to get more information that will help you determine whether to continue carrying each unsold item in the catalog. You can modify the query to include information from the stock table so that you can see a short description of each unsold item with its cost: 
SELECT c.catalog_num, c.stock_num, s.description, s.unit_price, 
   s.unit_descr, c.manu_code, i.quantity
FROM (catalog c INNER JOIN stock s
   ON c.stock_num = s.stock_num 
      AND c.manu_code = s.manu_code)
    LEFT JOIN items i
      ON c.stock_num = i.stock_num 
         AND c.manu_code = i.manu_code
   WHERE i.quantity IS NULL
      AND c.manu_code = "HRO";

In this example, an inner join between the catalog and stock tables forms the dominant part of an outer join with the items table.

For additional examples of outer joins, see the HCL OneDB™ Guide to SQL: Tutorial.