ON versus WHERE in an Outer Join
In an outer join a predicate in ON filters the match; the same predicate in WHERE quietly turns the join into an inner join.
FROM a LEFT JOIN b ON a.id = b.a_id AND b.status = 'paid'For an INNER JOIN it makes no difference where a predicate goes — ON a.id = b.a_id AND b.status = 'paid' and the same two conditions in WHERE produce the same rows and generally the same plan. For an outer join the position changes the answer, and this is the most common outer-join bug there is.
The order of operations
ON is evaluated first, deciding which right-hand rows count as a match. Only then does MySQL add the NULL-filled row for any left-hand row that matched nothing. WHERE runs last, on the output of that step — including the NULL-filled rows.
So a condition on the right-hand table in WHERE is tested against NULL for every unmatched row. NULL = 'paid' is NULL, not true, so those rows are filtered out and the LEFT JOIN has become an inner join. The manual is explicit: "For a LEFT JOIN, if the WHERE condition is always false for the generated NULL row, the LEFT JOIN is changed to an inner join." The optimizer performs that rewrite deliberately, so EXPLAIN will show an inner join where you wrote an outer one.
The exception
IS NULL is true for the generated row, so WHERE b.id IS NULL does not trigger the conversion — that is precisely how an anti-join works. Conditions on the preserved (left) table are also safe in WHERE.
Example
-- WRONG. The predicate runs after the NULL-extension. For a customer
-- with no paid order, o.status is NULL, NULL = 'paid' is not true,
-- and the row is filtered away. This is an inner join in disguise.
SELECT c.email,
COUNT(o.id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.email;
-- RIGHT. The predicate is part of the match test, so customers with
-- no paid order survive with NULL in o.* and a count of 0.
SELECT c.email,
COUNT(o.id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
AND o.status = 'paid'
GROUP BY c.id, c.email;When to use it
- Fix a LEFT JOIN report that lost every row without a matching child.
- Count only orders of a given status per customer while still listing customers with none.
- Read EXPLAIN to confirm whether a query ran an inner or an outer join.
- Restrict an optional joined table by date without shrinking the driving table.
More examples
Where each predicate belongs
The rule in one query: predicates on the preserved side go in WHERE, predicates on the NULL-able side go in ON. Putting a left-table condition in ON is not an error but is misleading, because it looks like it restricts the match when it actually only decides which left rows get a partner — every left row still appears.
SELECT c.email, o.id, o.placed_at
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
AND o.status = 'paid' -- right table: MUST be in ON
AND o.placed_at >= '2026-01-01' -- right table: MUST be in ON
WHERE c.country = 'FR' -- left table: safe in WHERE
AND c.deleted_at IS NULL -- left table: safe in WHERE
ORDER BY c.email;The IS NULL exception, and confirming it in the plan
Use EXPLAIN FORMAT=TREE as the settling argument when a report's row count looks wrong: the plan names the join type it actually ran, not the one you typed. The second query is the bug, and its plan will say inner.
-- IS NULL is TRUE for the generated NULL row, so this WHERE clause
-- does not convert the join. It keeps only the unmatched rows.
SELECT c.id, c.email
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.customer_id IS NULL;
-- Check which of the two happened. In FORMAT=TREE output an outer
-- join reads as 'Left hash join' or 'nested loop left join'; if it
-- reads 'Inner hash join', a WHERE predicate converted it.
EXPLAIN FORMAT=TREE
SELECT c.email
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid';For an inner join, position does not matter
Worth seeing so you do not over-apply the rule. With an inner join there is no NULL-extension step, so there is nothing for WHERE to filter that ON has not already. The convention most teams follow is join keys in ON, row filters in WHERE, purely for readability.
-- These two are equivalent: same rows, same plan.
SELECT o.id, c.email
FROM orders AS o
JOIN customers AS c
ON c.id = o.customer_id
AND c.country = 'FR';
SELECT o.id, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE c.country = 'FR';
-- The comma form is the same join again, with everything in WHERE.
SELECT o.id, c.email
FROM orders AS o, customers AS c
WHERE c.id = o.customer_id
AND c.country = 'FR';
Discussion