Moving a Predicate From ON to WHERE Breaks a LEFT JOIN
In a LEFT JOIN, a condition in the ON clause decides which right-hand rows count as a match, while a condition in WHERE is applied after the join has already filled unmatched rows with NULL. Because a comparison with NULL is never true, a WHERE condition on a right-hand column quietly throws away exactly the unmatched rows the LEFT JOIN was there to keep. Same words, different clause, different answer.
Questions this Concept answers
- Why does a `WHERE` condition on a right-hand column turn a `LEFT JOIN` into an inner join in practice?
Placing Filter Conditions in ON vs WHERE for SQL Inner and Outer Joins
Whether a filter condition is placed in a join's ON clause or in the query's WHERE clause is semantically irrelevant for an inner join but can change the result of an outer join, because the two clau…