AP 09 - Avoid filtering attributes from the non-preserved side of an outer join

Severity

Type

Problem

Trying to filter the non preserved side of an outer join is a bug in the logic of your code. The WHERE clause filters out unknown values. This means that all outer rows will be filtered out irrespectively. In essence you nullify the outer join.

SELECT a.col1
      ,b.col2
  FROM Customer a
  LEFT
  JOIN orders b
    ON a.customer_id = b.customer_id
 WHERE b.order_number <> 'x2345'

The value ‘x2345’ for the OrderID column in the Orders table filters out all rows from the OrderDetails table, implicitly converting your outer join to an inner join.

Solution

Rewrite your logic to cater for NULLs

SELECT a.col1
      ,b.col2
  FROM Customer a
  LEFT
  JOIN orders b
    ON a.customer_id=b.customer_id
 WHERE b.order_number<>'x2345'
    OR b.order_number IS NULL

An alternative solution would replace NULLs with a dummy value and then check against the dummy value. This solution is less readable than the previous solution

WHERE COALESCE(b.order_number,'unique')<>'x2345'

If you only wanted to show customers whose names were in the order table, you should make your outer join an inner join.

SELECT a.col1
      ,b.col2
  FROM Customer a
 INNER
  JOIN orders b
    ON a.customer_id=b.customer_id
 WHERE b.order_number<>'x2345'

Legitimate use of the anti pattern

The only time when putting a condition in the WHERE clause does not turn a LEFT JOIN into an INNER JOIN is when checking for NULL