E0023 outer-join-filtered

WHERE condition turns an outer join into an inner join.

What it reports

A WHERE condition, AND-ed with the rest, that compares a column of the optional side of an outer join (the right side of LEFT JOIN, the left side of RIGHT JOIN, either side of FULL JOIN). For the rows the join adds without a match that column is NULL, so the condition is not true and removes them: the outer join returns what an inner join would.

Example

Users with their number of paid orders, including users with none:

SELECT u.name, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.status = 'paid' GROUP BY u.id;

warning[E0023]: WHERE condition on 'o.status' removes the rows the outer join adds without a match in 'o': it works as an inner join
  --> q.sql:1:86
    |
  1 | SELECT u.name, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.status = 'paid' GROUP BY u.id;
    |                                                                                      ^^^^^^^^
    = help: Move the condition into the join's ON clause to keep unmatched rows, or write an inner JOIN (or test 'o.status IS NULL OR ...')

Fixed (users without paid orders are listed with 0):

SELECT u.name, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid' GROUP BY u.id;

Notes

Comparisons (=, <>, <, ...), IN (...), BETWEEN and LIKE with the column as an operand are reported. Conditions that hold for NULL (IS NULL, IS DISTINCT FROM, coalesce(...)), conditions inside OR, and statements with template tags are not.

Configuring

# sqlsift.toml
[rules]
outer-join-filtered = "error"   # or "off"

Or for a single line: -- sqlsift:disable E0023. The examples use the schema on the Rules page.