Why does a WHERE filter on the joined table remove unmatched rows specifically?

When a customer has no matching order, every column from the orders side — including status — comes back NULL for that row. A WHERE clause like status = 'paid' compares NULL to 'paid', which is neither true nor false (see the NULL filter post), so the row gets dropped, along with every other genuinely unmatched row.

So the fix is just moving the filter to ON?

For filtering the joined table specifically while still keeping unmatched left-side rows, yes: LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'. That applies the condition as part of deciding what counts as a match, not as a post-join filter that can eliminate the row entirely.

When should a filter go in WHERE instead of ON?

If you want to filter the left-hand (base) table — customers created this year, for instance — WHERE is correct and doesn't cause this problem, since it's not touching the joined table's nullable columns. The bug is specifically about filtering columns that come from the LEFT JOINed table.

Is this the same bug as the JOIN fan-out problem?

No — fan-out is about a join multiplying rows (numbers too high). This is about a join losing rows it was supposed to keep (numbers too low, specifically the unmatched ones). They're both JOIN mistakes but opposite in effect, and worth knowing apart.

How do I catch this if I don't know SQL well?

Ask directly: 'does this count include customers/rows with zero matches on the other table?' If the answer is meant to be yes and the query has a WHERE clause touching the joined table's columns, that's the thing to check first.

LEFT JOIN Acts Like INNER JOIN | WHERE vs ON Bug

You ask for "all customers, and their most recent order if they have one." The result only shows customers who do have an order. The ones with zero orders — which was the entire point of asking for "if they have one" — are just gone, with no error and a query that reads like it should obviously work.

This is one of the most common LEFT JOIN mistakes, and it happens because of where a filter condition is placed, not because LEFT JOIN itself is broken.

What LEFT JOIN promises, and how WHERE breaks that promise

LEFT JOIN is supposed to keep every row from the left (first) table, whether or not it finds a match on the right. For a customer with no orders, the right-hand columns (order_id, status, amount) all come back NULL — that row is still there, just with nothing on the order side.

The problem starts when a filter on one of those right-hand columns lands in WHERE:

SELECT c.name, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'

For a customer with zero orders, o.status is NULL. WHERE o.status = 'paid' evaluates NULL = 'paid', which is neither true nor false — the same three-valued-logic mechanic behind NULL rows disappearing from a plain filter — so the row gets dropped by WHERE, same as if it had failed any other condition. Every customer with no orders vanishes, and the query silently behaves exactly like an INNER JOIN would have, even though it's written as LEFT JOIN.

Why this is easy to write without noticing

"Customers and their paid orders" reads naturally as one filter — of course you'd only want paid ones. Nothing about writing the condition in WHERE looks wrong, and if you're mentally modeling "join the tables, then filter," WHERE is exactly where a filter belongs. The part that's counterintuitive is that this specific filter, on this specific side of the join, needs to happen as part of the join itself, not after.

The fix: move the condition into ON

SELECT c.name, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'

Now status = 'paid' is part of deciding what counts as a match, not a filter applied after matching. A customer with no paid orders (maybe none at all, maybe only unpaid ones) still gets a row — with o.status as NULL — instead of disappearing entirely. If you specifically want to filter the base table (customers created this year, say), that's fine to leave in WHERE, since it's not touching the joined table's nullable columns and doesn't have this failure mode.

The one-line way to tell these two placements apart

Condition in ON → "only match orders that are paid; keep the customer regardless"
Condition in WHERE → "keep only rows where a paid order matched; drop everyone else" — which quietly reintroduces INNER JOIN behavior

Same words, same table, same column — opposite result, depending purely on which clause the condition sits in.

How to catch it

Ask directly: "does this include customers with zero orders?" If the intended answer is yes, and the query has a WHERE clause referencing a column from the joined table, that's the first thing to check — especially if the actual row count looks suspiciously close to what an INNER JOIN would have returned.

The prompt that heads this off

When writing a LEFT JOIN, don't put filter conditions on the joined
table's columns in WHERE — that silently turns it into an INNER JOIN
by dropping every row with no match (their columns are NULL, and NULL
comparisons fail). Put those conditions in the ON clause instead, so
unmatched left-side rows are preserved.

The short version

LEFT JOIN keeps unmatched rows right up until a WHERE clause quietly un-keeps them. It's not a bug in LEFT JOIN — it's a filter landing on the wrong side of the join, and the fix is a two-word move from WHERE to ON.

Read-only MCP gateway: mcpserver.design.

Related reading: