SQL / JOINS
Join conditions in ON versus filters in WHERE
Decide, for every predicate in a join, whether it belongs in ON or WHERE, and explain why that choice is invisible on INNER JOIN but changes rows on an outer join.
What you will learn
- Place key equalities in ON and row filters in WHERE, then justify each placement
- Explain why ON and WHERE are interchangeable on INNER JOIN but not on LEFT JOIN
- Move a predicate on the null-supplied table into ON to keep unmatched rows
- Filter the preserved table in WHERE or a CTE, since ON cannot remove its rows
Understanding Join conditions in ON versus filters in WHERE
A join is evaluated in two stages, and ON and WHERE live in different stages. ON belongs to the FROM clause: it is the rule that decides which pairs of rows from the two inputs count as a match, and in an outer join it therefore also decides which rows end up unmatched and get padded with NULLs. WHERE runs afterwards, on the single wide row set the join has already produced. The mental model worth keeping is that ON builds a table and WHERE filters the table that was built.
For an INNER JOIN the two stages collapse, because an inner join keeps only matched pairs and never pads anything with NULLs. Every condition in ON and every condition in WHERE is simply ANDed against the same candidate pairs, so putting o.status = 'paid' in ON or in WHERE returns identical rows, and planners usually produce the same plan either way. That equivalence is why the distinction feels academic, and why it bites the first time someone changes JOIN to LEFT JOIN and the row count quietly shrinks back.
In an outer join both the stage and the table being tested matter. A predicate on the null-supplied table inside ON narrows what counts as a match while every preserved row still appears, padded with NULLs; the same predicate in WHERE is checked after the padding, where NULL = 'paid' is unknown rather than true, so it deletes exactly those preserved rows. The mirror case surprises people just as often: a predicate on the preserved table inside ON cannot delete preserved rows either, it only forbids them from matching, so LEFT JOIN emp e ON e.dept_id = d.id AND d.active = 1 returns inactive departments with NULL employees. To genuinely remove preserved rows, filter them in WHERE or in a subquery before the join.
placeholder
WITH customers(id, name) AS (
VALUES (1, 'Ada'), (2, 'Brij'), (3, 'Cleo')
),
orders(id, customer_id, placed_on, amount) AS (
VALUES (10, 1, '2025-11-02', 40),
(11, 1, '2026-01-15', 55),
(12, 2, '2025-08-30', 20)
)
SELECT 'ON' AS written_in, c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.placed_on >= '2026-01-01'
UNION ALL
SELECT 'WHERE', c.name, o.id, o.amount
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
WHERE o.placed_on >= '2026-01-01'
ORDER BY written_in, name;ON decides which row pairs count as a match while the join result is being built, and WHERE filters that result afterwards, which is why the two are interchangeable for inner joins and not for outer joins.
Worked examples
ON and WHERE agree on an inner join
The same predicate moved between ON and WHERE produces identical rows once the join is an INNER JOIN.
WITH person(id, name) AS (
VALUES (1, 'Ada'), (2, 'Brij')
),
visit(person_id, day_name) AS (
VALUES (1, 'mon'), (1, 'tue'), (2, 'mon')
)
SELECT 'ON' AS variant, p.name, v.day_name
FROM person p
JOIN visit v ON v.person_id = p.id AND v.day_name = 'mon'
UNION ALL
SELECT 'WHERE', p.name, v.day_name
FROM person p
JOIN visit v ON v.person_id = p.id
WHERE v.day_name = 'mon'
ORDER BY variant, name;Example explained
Line 1The first branch tests v.day_name = 'mon' while pairs are being matched; the second tests it after the join is complete.
Line 2An inner join discards unmatched rows instead of padding them, so there are no NULL-extended rows for the WHERE test to behave differently on.
Line 3Both branches return Ada and Brij with 'mon', so here the placement is a readability decision, not a correctness one.
A preserved-side test inside ON
A condition on the left table written in ON blocks matching but never removes the left row.
WITH dept(id, name, active) AS (
VALUES (1, 'Sales', 1), (2, 'Legacy', 0)
),
emp(dept_id, name) AS (
VALUES (1, 'Ada'), (2, 'Brij')
)
SELECT d.name AS dept_name, e.name AS emp_name
FROM dept d
LEFT JOIN emp e
ON e.dept_id = d.id
AND d.active = 1
ORDER BY d.id;Example explained
Line 1d.active = 1 sits in ON, so it only decides whether Legacy is allowed to match a row in emp.
Line 2Legacy fails that test, matches nothing, and is NULL-extended, so Legacy stays in the result while its employee Brij vanishes.
Line 3Moving d.active = 1 to WHERE drops the Legacy row itself, because a filter on the preserved table is never made unknown by padding.
ON restricts, WHERE finds the gaps
An intentional pairing of an ON predicate with a WHERE IS NULL test to list rows that have no matching row of a particular kind.
WITH student(id, name) AS (
VALUES (1, 'Ada'), (2, 'Brij'), (3, 'Cleo')
),
exam(student_id, term, score) AS (
VALUES (1, 'fall', 88), (2, 'spring', 71)
)
SELECT s.name
FROM student s
LEFT JOIN exam x
ON x.student_id = s.id
AND x.term = 'fall'
WHERE x.student_id IS NULL
ORDER BY s.id;Example explained
Line 1The ON clause defines a match as a fall exam, so Brij's spring exam does not count and Brij is NULL-extended.
Line 2WHERE x.student_id IS NULL then keeps only the padded rows, which are exactly the students without a fall exam.
Line 3Testing x.term = 'fall' in WHERE instead would contradict x.student_id IS NULL and return zero rows.
Important notes
On an inner join the choice is pure style, so settle on one convention, keys in ON and filters in WHERE, and keep it consistent.
USING (customer_id) leaves nowhere to add an extra match condition, so rewrite it as ON when you need one; and a later INNER JOIN in the same query can still delete the NULL-extended rows an earlier LEFT JOIN produced.
Common mistakes
Forming the habit on inner joins, where both placements work, and keeping it after switching to LEFT JOIN: the filter in WHERE removes every NULL-extended row and the query silently returns inner-join results again.
Writing a filter on the preserved table in ON, such as AND d.active = 1, expecting those rows to disappear; instead they arrive with all-NULL columns from the other side and inflate COUNT(*), SUM and GROUP BY results.
Moving one predicate into ON but leaving a second predicate on the same table in WHERE; the leftover test is still unknown for padded rows, so the rows are dropped again and the fix looks like it did nothing.
Try it yourself
Change, predict, then run
In the browser editor, build a three-row region CTE and a sale CTE in which only one region has a 2026 sale, then write the same LEFT JOIN twice, once with the year test in ON and once in WHERE. Confirm you get three rows from the first and one row from the second.
Open the SQL workspaceCheck your understanding
A LEFT JOIN from store to promo reads ON p.store_id = s.id AND p.channel = 'email'. store has 12 rows, and only 3 stores have an email promo (at most one each). How many rows does the query return?
- 3, because the extra ON condition filters the result down to matching stores
- 9, because stores whose promo is not an email promo are excluded
- 12, with NULL promo columns for the 9 stores that have no email promo
- It depends on whether the channel test is written before or after the key comparison in ON
Show answer
The ON clause only decides which promo rows count as a match, and a LEFT JOIN keeps every store row regardless, padding the 9 non-matching stores with NULLs, so 12 rows come back. The answer 3 is what you would get with p.channel = 'email' in WHERE, because after padding that test evaluates to unknown for those 9 rows and unknown rows are discarded. Order inside ON is irrelevant, since the conditions are simply ANDed.