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 Informix® Performance Guide.