LEFT JOIN with a WHERE clause: why rows disappear (and how to fix it)

You wrote LEFT JOIN because you wanted every buyer, including the ones who never bought anything. Then you added one filter, WHERE o.status = 'paid', and the result shrank. No error, no warning: the query now answers a different question.

Try it first, before the numbers below:

SELECT u.id, COUNT(o.id) AS paid_orders
FROM users u
LEFT JOIN orders o ON o.buyer_id = u.id
WHERE o.status = 'paid'
GROUP BY u.id;

Here is the same request written two ways on the Meridian practice marketplace, which has 7,000 registered buyers.

-- Condition in WHERE
SELECT b.buyer_id, COUNT(o.order_id) AS paid_orders
FROM meridian.buyers AS b
LEFT JOIN meridian.orders AS o ON o.buyer_id = b.buyer_id
WHERE o.status = 'paid'
GROUP BY b.buyer_id;
-- 3,652 rows

-- Condition in ON
SELECT b.buyer_id, COUNT(o.order_id) AS paid_orders
FROM meridian.buyers AS b
LEFT JOIN meridian.orders AS o
  ON o.buyer_id = b.buyer_id AND o.status = 'paid'
GROUP BY b.buyer_id;
-- 7,000 rows, 3,348 of them with paid_orders = 0

The first query lost 3,348 buyers, almost half of them. Anything you build on top changes with it: the average number of paid orders per buyer is 2.48 in the first result and 1.30 in the second. Only the second one is "per registered buyer".

The first rows of each result show where they went:

Why WHERE undoes the LEFT JOIN

SQL builds the joined rows first and filters them afterwards. The LEFT JOIN does its job: a buyer with no matching order stays, with NULL in every column from orders. Buyers with only cancelled orders still have matching rows at this stage. Then WHERE runs, row by row, and keeps only the rows where the condition is true, which removes both groups.

That is where NULL comes in. NULL means "unknown", so any comparison with it is also unknown: NULL = 'paid' is not true and not false. WHERE throws away everything that isn't true, so the buyers the join kept are removed one step later. The same happens with o.status <> 'cancelled', o.paid_eur > 0 or any other ordinary comparison on the right table. The result is exactly what an inner join would have returned.

WHERE vs ON: which rows each one filters

A practical rule for LEFT JOIN:

• Conditions on the right table go in ON when you want to keep every left row.
• Conditions on the left table go in WHERE. In ON they don't filter anything: ON o.buyer_id = b.buyer_id AND b.city = 'Riga' still returns all 7,000 buyers; the ones outside Riga just get no orders attached.

For an inner JOIN the two places give the same result for conditions like these, which is why the habit of "filters go in WHERE" feels safe until the first LEFT JOIN.

SELECT u.id, COUNT(o.id) AS paid_orders
FROM users u
LEFT JOIN orders o ON o.buyer_id = u.id
WHERE o.status = 'paid'
GROUP BY u.id;

The half-fix: OR o.status IS NULL

A common repair is to let the NULLs through:

WHERE o.status = 'paid' OR o.status IS NULL

On Meridian that returns 6,947 buyers, not 7,000. The missing 53 are buyers whose only orders were cancelled. Their joined rows have status = 'cancelled', which is neither 'paid' nor NULL, so they still disappear. The query looks fixed and is still wrong. Move the condition into ON instead.

Finding rows with no match: the IS NULL anti-join

Sometimes you want only the rows without a match: buyers who never paid, products nobody ordered. Here a WHERE on the right table is deliberate:

SELECT COUNT(*) AS buyers_without_paid_order
FROM meridian.buyers AS b
LEFT JOIN meridian.orders AS o
  ON o.buyer_id = b.buyer_id AND o.status = 'paid'
WHERE o.order_id IS NULL;
-- 3,348

Two details make it correct:

1. Test a column that is never NULL in a real match, usually the right table's key. Testing a column that can be empty in real orders, such as o.promo_code, would also keep buyers whose matched orders simply have no promo code.
2. Keep the status filter in ON. Leave it out and you get 3,295: the buyers who never ordered at all, a different question. Put it in WHERE next to IS NULL and you get zero rows, because a row can't be both 'paid' and NULL.

NOT EXISTS gives the same 3,348 and some people find it easier to read:

SELECT COUNT(*)
FROM meridian.buyers AS b
WHERE NOT EXISTS (
  SELECT 1 FROM meridian.orders AS o
  WHERE o.buyer_id = b.buyer_id AND o.status = 'paid'
);

Be careful with NOT IN (SELECT ...): if the subquery returns a single NULL, the condition is never true and the query returns no rows.

How to spot it

• Count the left table, then count the result. SELECT COUNT(*) FROM meridian.buyers returns 7,000. A LEFT JOIN grouped by buyer must return 7,000 rows too. Fewer means something removed buyers after the join.
• Read the WHERE clause for right-table columns. Any o.something = … or o.something > … under a LEFT JOIN … o is a suspect, unless it is an intentional IS NULL test.
• Look for zeros. A "per buyer" count with no zeros in it, on a marketplace where many people never buy, is a hint that the zeros were filtered out.
• Watch the denominator. An average or a share that looks too good may have lost the people who did nothing.
• Check the rest of the query too. A later inner JOIN to another table, or HAVING COUNT(o.order_id) > 0, removes the same rows.

When the inner join is what you meant

If the question is "how many orders does each paying buyer have?", dropping buyers with no paid order is correct. Then say so in the code: write JOIN instead of LEFT JOIN ... WHERE. The next reader, or the AI assistant editing your query, won't have to guess whether the rows were removed on purpose.

Practise it with your own predictions

Atterna Start introduces INNER JOIN and LEFT JOIN on day 8, including finding products nobody has ordered, and day 9 is about joins that multiply rows. You predict the row count, run the query on the fictional Meridian data and get an automatic check. Days 1–2 run in your browser with no account (start day 1), and a free account opens all ten, JOIN days included. The opposite problem, a join that adds rows and doubles a total, is in why JOIN duplicates rows.

FAQ

Does a WHERE clause turn a LEFT JOIN into an INNER JOIN?

It does when the WHERE compares a column from the right table with a value, for example WHERE o.status = 'paid'. Rows without a match have NULL in that column, the comparison isn't true, and they are removed. The result is the same as an inner join. A condition such as WHERE o.order_id IS NULL is different: it keeps exactly the unmatched rows.

What is the difference between ON and WHERE in a LEFT JOIN?

ON decides which rows from the right table match each left row; left rows are kept either way. WHERE filters the joined result and can remove left rows. Put conditions on the right table in ON if every left row must stay, and conditions on the left table in WHERE.

Why does my LEFT JOIN return fewer rows than the left table?

A LEFT JOIN on its own never returns fewer rows than the left table. If you get fewer, something after the join removed them: a WHERE on a right-table column, a later inner JOIN, a HAVING filter or a LIMIT. Grouping by the left key can also reduce rows by design, but then you get one row per left key, not fewer keys.

How do I find rows in one table that have no match in another?

Use a LEFT JOIN with the match conditions in ON and WHERE right_table.key IS NULL, or use NOT EXISTS. Avoid NOT IN with a subquery that can contain NULL, because it then returns no rows.

Can AI-generated SQL make this mistake?

Yes. It is a common pattern in hand-written and generated SQL because the query runs and the result looks plausible. Ask whether every left-table row should survive and compare the row count before and after the join. The AI-generated SQL mistakes guide has five more patterns like this one.