Why a WHERE filter turns a LEFT JOIN into an INNER JOIN

Start with the zero-row contract

Suppose a billing summary must list every customer and how many paid orders they have placed. For a customer with no paid orders, the report should still include that customer with a count of 0. That requirement is why the query author reaches for a LEFT JOIN instead of an INNER JOIN.

In the three-customer fixture used by the companion explorer, Ada has one paid order and one refunded order, Linus has one refunded order, and Grace has no orders at all. Before reading the SQL clauses, write down the expected output: Ada should show 1, Linus should show 0, and Grace should show 0.

-- Expected paid-order summary:
-- Ada   -> 1
-- Linus -> 0
-- Grace -> 0

How WHERE eliminates NULL-extended rows

When you write LEFT JOIN orders o ON o.customer_id = c.id, the join step pairs Grace with a single row whose order columns are all NULL. If you then add WHERE o.status = 'paid', the database evaluates NULL = 'paid' for Grace’s row. In SQL three-valued logic, that comparison is unknown rather than true, so the WHERE clause removes Grace before GROUP BY runs.

Switch the explorer to LEFT JOIN with WHERE o.status = 'paid'. Both Linus (whose only order is refunded) and Grace (whose joined columns are NULL) disappear from the final table. Only Ada remains. The query still says LEFT JOIN in the source file, but the post-join WHERE predicate has turned its behavior into an INNER JOIN.

SELECT c.id, c.name, COUNT(o.id) AS matched_orders
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.name;

Move the joined-table predicate into ON

Now move the status check into the join condition: ON o.customer_id = c.id AND o.status = 'paid'. During the join step, Linus’s refunded order fails the ON condition, so Linus is extended with NULL order columns just like Grace. Because there is no WHERE filter on the right-hand table afterward, both NULL-extended rows survive into GROUP BY.

Keep this distinction in mind during code review: a condition inside ON decides which right-hand rows attach to each left-hand row, whereas a condition inside WHERE decides which combined rows survive after the join has already completed.

Why COUNT(*) and COUNT(o.id) disagree on unmatched groups

Preserving the NULL-extended row solves only half of the problem. With the filter in ON, switch the explorer’s aggregate from COUNT(o.id) to COUNT(*). Suddenly Linus and Grace both report 1 instead of 0. COUNT(*) counts every row in the group, and a NULL-extended left join row is still one row.

COUNT(o.id) counts only rows where o.id is non-NULL. Because the primary key of an unmatched right-hand row is NULL, COUNT(o.id) returns 0 for Linus and Grace while returning 1 for Ada. Similarly, SUM(o.amount) returns NULL when every value in the group is NULL, so wrapping it in COALESCE(SUM(o.amount), 0) makes the zero explicit.

SELECT c.id, c.name, COUNT(o.id) AS matched_orders
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY c.id;

When a WHERE check on the right table is intentional

There is one common case where filtering the right table in WHERE is exactly what you want: finding left-hand rows that have no match at all. Select WHERE o.id IS NULL in the explorer under LEFT JOIN. Only Grace survives, forming an anti-join.

When reviewing a query that mixes LEFT JOIN, WHERE and GROUP BY, trace one matched row, one row with non-matching status, and one row with no right-hand records at all. Those three cases expose both dropped groups and off-by-one counts before the query reaches production.