Skip to content

Joins · Medium

In a LEFT JOIN, what is the difference between putting a filter in ON and in WHERE?

Short answer

A condition in ON decides which right-side rows match, so unmatched left rows are still kept with NULLs. The same condition in WHERE runs after the join and discards those NULL rows, quietly turning the LEFT JOIN into an INNER JOIN. Put right-table filters in ON when you want to keep every left-side row.

Detailed answer

Task: list every customer with the number of delivered orders they have, including zero.

-- Correct: the status filter is part of the match condition
SELECT c.full_name,
       COUNT(o.order_id) AS delivered_orders
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.status = 'delivered'
GROUP BY c.customer_id, c.full_name
ORDER BY delivered_orders, c.full_name;
-- Subtly wrong: WHERE removes customers with no delivered order
SELECT c.full_name,
       COUNT(o.order_id) AS delivered_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
GROUP BY c.customer_id, c.full_name
ORDER BY delivered_orders, c.full_name;

The second query never returns a zero, because for customers without a delivered order o.status is NULL and NULL = 'delivered' is not true.

Rule of thumb: filters on the preserved (left) table go in WHERE; filters on the optional (right) table go in ON. Also note COUNT(o.order_id) rather than COUNT(*) — the latter would count the NULL-padded row as 1.