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