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.

customer_num	company	phone	order_date
101	All Sports Supplies	408-789-8075	05/21/2008
102	Sports Spot	415-822-1289	NULL
103	Phil’s Sports	415-328-4543	NULL
104	Play Ball!	415-368-1100	05/20/2008
—	—	—	—

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.

customer_num	company	phone	order_date
101	All Sports Supplies	408-789-8075	NULL
102	Sports Spot	415-822-1289	NULL
103	Phil’s Sports	415-328-4543	NULL
104	Play Ball!	415-368-1100	05/20/2008
—	—	—	—

Even though the order date for customer number 101 is 05/21/2008 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.