Getting Started › Left Outer Joins › WHERE Clause

WHERE Clause

The WHERE clause can be used with the LEFT OUTER JOIN, but since the WHERE clause is conceptually executed after the FROM clause (which includes executing the LEFT OUTER JOIN), references to columns in tables on the right side of a LEFT OUTER JOIN are evaluated against the null value for non-matching rows. This means that unless IS NULL is the predicate (or it is under an OR that has an IS NULL predicate), the predicate result is unknown, and the row is eliminated. Eliminating unmatched rows therefore turns your LEFT OUTER JOIN into an INNER JOIN.

For example, if you want to modify the previously shown query to only return ORDERS data for the current date, but you still want to see all CUSTOMER rows:

 SELECT T1.NAME, VALUE (SUM(T2.AMOUNT), 0), COUNT(DISTINCT T3.ORDNO)
 FROM CUSTOMER T1 LEFT JOIN (ORDERS T2 INNER JOIN LINE_ITEM T3
                             ON T2.ORDNO = T3.ORDNO)
      ON T1.CUSTNO = T2.CUSTNO
 WHERE T2.ORDER_DATE = CURRENT DATE OR T2.ORDER_DATE IS NULL
 GROUP BY T1.NAME;

Without the OR T2.ORDER_DATE IS NULL you would not get back CUSTOMERS that have no matching ORDERS rows.

As the following example shows, by placing the predicate in the ON clause you do not have to add the OR IS NULL predicate, because the ON clause is evaluated before the columns of the non-matching row are set to the null value:

 SELECT T1.NAME, VALUE (SUM(T2.AMOUNT), 0), COUNT(DISTINCT T3.ORDNO)
 FROM CUSTOMER T1 LEFT JOIN (ORDERS T2 INNER JOIN LINE_ITEM T3
                              ON T2.ORDNO = T3.ORDNO AND
                                 T2.ORDER_DATE = CURRENT DATE)
      ON T1.CUSTNO = T2.CUSTNO
 GROUP BY T1.NAME;