You want every customer in a report, including the ones who didn’t buy anything, so you write LEFT JOIN. Then you add “only paid orders from September” to the WHERE clause, and half the customers disappear. The query has no error and the numbers for the remaining customers are correct, which is why this bug survives code review so often. It’s one of the most common SQL interview questions for exactly that reason.
The results here were run on PostgreSQL 16.15, but the behaviour is defined by the SQL standard and is identical in MySQL, SQL Server, SQLite and Oracle.
The report that loses customers
CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE orders (
id int PRIMARY KEY, customer_id int NOT NULL REFERENCES customers,
status text NOT NULL, created_at date NOT NULL, total numeric(10,2) NOT NULL
);
INSERT INTO customers VALUES (1,'Ana'), (2,'Ben'), (3,'Chen'), (4,'Dee');
INSERT INTO orders VALUES
(10, 1, 'paid', '2026-09-03', 40),
(11, 1, 'refunded', '2026-09-10', 25),
(12, 2, 'refunded', '2026-09-12', 60),
(13, 3, 'paid', '2026-08-28', 90),
(14, 3, 'paid', '2026-09-20', 15);Ben has only a refund. Dee has never ordered. The task: every customer with their count and value of paid September orders, zeros included.
SELECT c.name, count(o.id) AS sept_paid_orders, coalesce(sum(o.total), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
AND o.created_at >= '2026-09-01'
GROUP BY c.id, c.name
ORDER BY c.id; name | sept_paid_orders | revenue
------+------------------+---------
Ana | 1 | 40.00
Chen | 1 | 15.00Ben and Dee are gone. The query author did everything else right: count(o.id) instead of count(*), a coalesce around the sum. Those protections never got a chance to work.
ON and WHERE run at different moments
It helps to think about the logical order of evaluation (the database may physically reorder things, but the result must match this):
FROM ... LEFT JOIN ... ONbuilds the joined rows. For each customer, every order satisfying theONcondition is paired with it. If none satisfies it, the customer still gets one row, with NULL in everyorderscolumn.WHEREthen filters those finished rows.GROUP BY, aggregates andSELECTcome after.
Walk Dee through it. Step 1: she has no orders, so she gets one row: (4, 'Dee', NULL, NULL, NULL, ...). Step 2: WHERE o.status = 'paid' evaluates NULL = 'paid', which is unknown, not true, and WHERE keeps only true rows. Dee’s row is discarded.
Ben is subtler. Step 1 pairs him with his refunded order, so his row has o.status = 'refunded'. Step 2 rejects it as not paid. His only row is gone, so he’s gone too.
The left join did its job. The WHERE clause removed precisely the rows the left join had added, which makes the whole query behave like an inner join. PostgreSQL notices this and simplifies the plan accordingly. For a smaller version of the query:
EXPLAIN (COSTS OFF)
SELECT c.name, o.id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'; Nested Loop
-> Seq Scan on orders o
Filter: (status = 'paid'::text)
-> Index Scan using customers_pkey on customers c
Index Cond: (id = o.customer_id)There’s no Left Join anywhere in that plan. The planner proved that the WHERE clause rejects NULL-extended rows (“strict” in PostgreSQL’s terms), so it turned the outer join into an inner join. If you ever see an inner join in the plan of a query you wrote as LEFT JOIN, this is usually why.
Move conditions about the optional table into ON
The ON clause is part of the matching step. Conditions there decide which orders count as a match; they can’t remove the customer.
SELECT c.name, count(o.id) AS sept_paid_orders, coalesce(sum(o.total), 0) AS revenue
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.status = 'paid'
AND o.created_at >= '2026-09-01'
GROUP BY c.id, c.name
ORDER BY c.id; name | sept_paid_orders | revenue
------+------------------+---------
Ana | 1 | 40.00
Ben | 0 | 0
Chen | 1 | 15.00
Dee | 0 | 0Now Ben’s refunded order doesn’t match the ON, so he gets a NULL-extended row like Dee, and count(o.id) turns that into 0. Chen’s August order doesn’t match either, so only his September order is counted.
The rule that falls out:
- Conditions on the optional (right) table that define what counts as a match go in
ON. - Conditions on the preserved (left) table go in
WHERE.WHERE c.region = 'EU'is fine; it filters customers, which is what you meant. - A
WHEREtest on the right table is correct only when you mean “rows that have no match should go”, most commonly the anti-joinWHERE o.id IS NULL.
Two fixes that don’t work
Adding OR o.id IS NULL. This tries to rescue unmatched customers in the WHERE clause:
... LEFT JOIN orders o ON o.customer_id = c.id
WHERE (o.status = 'paid' AND o.created_at >= '2026-09-01') OR o.id IS NULL name | sept_paid_orders
------+------------------
Ana | 1
Chen | 1
Dee | 0Dee is back because she has no orders at all. Ben is still missing: he does have an order, so his row isn’t NULL-extended, and the refund fails the first condition. IS NULL after the join can only tell you “no order at all”, not “no order that matches my criteria”. Those are different questions.
Putting a left-table condition in ON. The reverse mistake is less common but confusing. LEFT JOIN orders o ON o.customer_id = c.id AND c.name = 'Ana' doesn’t restrict the result to Ana. It returns all four customers; only Ana gets her orders attached, and everyone else gets NULLs. A left join never removes rows from the left table, whatever you put in ON.
Filtering after aggregation
Sometimes the question is about the aggregated result: “customers with no paid September orders”. Build the correct left join with conditions in ON, then filter the groups with HAVING count(o.id) = 0, or use NOT EXISTS with the conditions inside the subquery. The EXISTS guide covers the second approach, which is often clearer when you don’t need the aggregated numbers.
If the conditions on the right table get long, another readable option is to filter first in a subquery or CTE, then left join to it:
WITH sept_paid AS (
SELECT * FROM orders
WHERE status = 'paid' AND created_at >= '2026-09-01'
)
SELECT c.name, count(s.id) AS sept_paid_orders
FROM customers c
LEFT JOIN sept_paid s ON s.customer_id = c.id
GROUP BY c.id, c.name;This is equivalent to the ON version. Some teams prefer it because the join condition stays a pure key match and the business rules live in a named step.
Interview practice: the chained left join
A query lists every employee with their department name and the name of their current manager, if any:
SELECT e.name, d.name AS dept, m.name AS manager
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id
LEFT JOIN employees m ON m.id = e.manager_id
WHERE d.active = true;Employees without a department are missing from the output. Why, and how would you fix it so they appear with a NULL department?
Answer
WHERE d.active = true tests a column from the optional departments side. For an employee with no department, the first left join gives d.active = NULL, the WHERE evaluates to unknown, and the row is dropped. The planner will reduce that first left join to an inner join. The manager join isn’t affected; it’s still a real left join.
The fix depends on what “active” is supposed to mean. If inactive departments should be hidden but the employee kept, move the condition into the ON: LEFT JOIN departments d ON d.id = e.department_id AND d.active. An employee in an inactive department then shows a NULL department, same as one with no department. If the intent was “exclude employees whose department is inactive, but keep those without a department”, the condition is WHERE d.id IS NULL OR d.active, and here the IS NULL test is correct because “no department at all” is exactly the case being kept. I’d ask which behaviour is wanted, since they differ for employees in inactive departments, and add a test row for each case: no department, inactive department, active department.