SQLIntermediate
Why did this LEFT JOIN behave like an INNER JOIN?
“Someone put a condition on the right table in WHERE and the outer join stopped returning unmatched rows. Explain.”
What this tests
- ON vs WHERE semantics on outer joins
Answers by level
Read the beginner answer first and notice what is missing.
A LEFT JOIN keeps unmatched left rows with NULLs on the right. A condition on a right-side column in WHERE runs after the join and is NULL for those rows — and WHERE keeps only true — so the unmatched rows are dropped, turning the outer join into an inner one.
The fix: conditions about the nullable (right) side go in ON; conditions about the preserved (left) side go in WHERE.
Green flags · Red flags
Strong green flag · Explains it via NULL not being true rather than by rote.
Green flags
- States the ON-vs-WHERE rule precisely
- Knows the anti-join idiom is the intended exception
Red flags
- Cannot explain why
- Thinks LEFT and INNER are interchangeable
Follow-up questions
F1
Where does a filter like status = 'paid' belong on the right table of a LEFT JOIN?
Scenario
"All customers with their paid orders" is missing every customer who has no paid orders. The join is a LEFT JOIN. Fix it.