Using the ON Clause

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

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;

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.

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";

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.