What is the difference between putting a filter in ON versus WHERE in a LEFT JOIN?
A filter on the right table in ON limits which rows match while still keeping every left row. The same filter in WHERE runs after the join and removes the NULL-extended rows, turning the LEFT JOIN into an INNER JOIN.
Explain it simply
You want all students listed, along with their 2026 fee receipt if they have one. If the "2026 only" rule is part of matching (ON), students without a 2026 receipt still appear, with a blank. If you apply the rule afterwards (WHERE), every student with a blank is thrown out.
Example
Tables: students (Asha, Ravi) and fees with one row, Asha paid in 2026.
-- filter in ON, keeps Ravi
SELECT s.name, f.year
FROM students s
LEFT JOIN fees f ON f.student_id = s.id AND f.year = 2026;| name | year |
|---|---|
| Asha | 2026 |
| Ravi | NULL |
-- filter in WHERE, loses Ravi
SELECT s.name, f.year
FROM students s
LEFT JOIN fees f ON f.student_id = s.id
WHERE f.year = 2026;| name | year |
|---|---|
| Asha | 2026 |
Ravi's joined row has f.year = NULL, and NULL = 2026 is not true, so WHERE discards it.
For an INNER JOIN the two placements give the same result.
What to say in the interview
"For outer joins, ON decides what matches, WHERE filters the final rows. A WHERE condition on the nullable side defeats the outer join. Conditions on the preserved table are safe in WHERE."
Common mistakes
- Putting a right-table filter in WHERE and wondering why unmatched rows vanished.