Solved

My LEFT JOIN behaves like an INNER JOIN when I add a WHERE clause

Asked by Rafael Mendes · 840 views · 3 replies

Rafael Mendes8 messages · 1 solution

I want every customer plus their orders from this year, including customers who ordered nothing. This is what I have:

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2026-01-01';

Customers with no orders disappear completely. If I remove the WHERE they come back, so the join itself is fine. What am I missing?

3 replies

Hannah LindqvistSolution

The join is doing its job — the WHERE is undoing it afterwards.

A LEFT JOIN gives you the customer row with o.total and o.created_at set to NULL when there is no matching order. Then WHERE o.created_at >= '2026-01-01' runs over that result, and NULL >= '2026-01-01' is not true, so the row is discarded. You have filtered away exactly the rows the LEFT JOIN was there to keep.

Move the condition into the ON clause, where it decides which orders match rather than which rows survive:

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.created_at >= '2026-01-01';

Rule of thumb: on the nullable side of an outer join, a condition in WHERE is an inner join in disguise. The only WHERE condition that keeps the outer behaviour is an explicit IS NULL check, which is a different query — that one finds customers who did not order.

Link
Rafael Mendes

That is a much clearer way to think about it than the diagrams I had been staring at. Moved it into ON and the customers are back. Thank you.

Link
Priya Nair

Worth adding the variant that bites next, because it looks the same and is not:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

That is the deliberate use of a WHERE on the nullable side — an anti-join, finding customers with no orders at all. Same shape, opposite intent. If you ever see IS NULL there, it is probably on purpose; anything else probably is not.

Link