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 HCL OneDB™ Performance Guide.