Using the ON Clause

Use the ON clause to specify the join condition and any expressions as optional join filters.

The following example from the stores_demo database illustrates how the join condition in the ON clause combines the customer and orders tables:

SELECT c.customer_num, c.company, c.phone, o.order_date
   FROM customer c LEFT JOIN orders o
      ON c.customer_num = o.customer_num

The following table shows part of the joined customer and orders tables.

In an outer join, the join filters (expressions) that you specify in the ON clause determine which rows of the subordinate table join to the dominant (or outer) table. The dominant table, by definition, returns all its rows in the joined table. That is, a join filter in the ON clause has no effect on the dominant table.

If the ON clause specifies a join filter on the dominant table, the database server joins only those dominant table rows that meet the criterion of the join filter to rows in the subordinate table. The joined result contains all rows from the dominant table. Rows in the dominant table that do not meet the criterion of the join filter are extended with NULL values for the subordinate columns.

The following example from the stores_demo database illustrates the effect of a join filter in the ON clause:

SELECT c.customer_num, c.company, c.phone, o.order_date
   FROM customer c LEFT JOIN orders o
      ON c.customer_num = o.customer_num 
         AND c.company <> "All Sports Supplies"

The row that contains All Sports Supplies remains in the joined result.

Even though the order date for customer number 101 is 05/21/1998 in the orders table, the effect of placing the join filter (c.company <> "All Sports Supplies") prevents this row in the dominant customer table from being joined to the subordinate orders table. Instead, a NULL value for order_date is extended to the row of All Sports Supplies.

Applying a join filter to a base table in the subordinate part of an outer join can improve performance. For more information, see your IBM Informix Performance Guide.

Specifying a Post-Join Filter

When you use the ON clause to specify the join, you can use the WHERE clause as a post-join filter. The database server applies the post-join filter of the WHERE clause to the results of the outer join.

The following example illustrates the use of a post-join filter. This query returns data from the stores_demo database. Suppose you want to determine which items in the catalog are not being ordered. The next query creates an outer join of the data from the catalog and items tables and then determines which catalog items from a specific manufacturer (HRO) have not sold:

SELECT c.catalog_num, c.stock_num, c.manu_code, i.quantity 
FROM catalog c 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"

The WHERE clause contains the post-join filter that locates the rows of HRO items in the catalog for which nothing has been sold.

When you apply a post-join filter to a base table in the dominant or subordinate part of an outer join, you might improve performance. For more information, see your IBM Informix Performance Guide.

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.

Additional Examples of Outer Joins

For additional examples of outer joins, see the IBM Informix Guide to SQL: Tutorial.