LEFT JOIN with WHERE: why rows disappear
A WHERE condition on orders can remove customers with no matching paid order. Put the condition in ON to keep every customer and show NULL for missing matches.
By QueryPlan. Published . Updated .
On this page
Start with the source rows
The report should include every customer and show a paid order's total when one exists. Ada has a paid order, Linus has a pending order, and Grace has no order. The orders.customer_id value identifies the customer who placed each order. Linus has a related order that fails the paid condition, while Grace has no related order.
Swipe or scroll the table to see all columns.
| id | name |
|---|---|
| 1 | Ada |
| 2 | Linus |
| 3 | Grace |
Swipe or scroll the table to see all columns.
| id | customer_id | status | total |
|---|---|---|---|
| 1 | 1 | paid | 40 |
| 2 | 2 | pending | 30 |
Schema and sample SQL
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT, total INTEGER);INSERT INTO customers VALUES (1,'Ada'),(2,'Linus'),(3,'Grace');
INSERT INTO orders VALUES (1,1,'paid',40),(2,2,'pending',30);Before: filter after the join
The first query joins orders on customer_id, then applies WHERE o.status = 'paid'. Ada's row passes. Linus's pending order fails the condition. Grace has NULL in the order columns, and comparing NULL with 'paid' does not return true. WHERE therefore removes Linus and Grace, leaving only Ada with a total of 40.
SELECT c.id,c.name,o.total FROM customers c LEFT JOIN orders o ON o.customer_id=c.id WHERE o.status='paid' ORDER BY c.id;Swipe or scroll the table to see all columns.
| id | name | total |
|---|---|---|
| 1 | Ada | 40 |
After: filter the matching orders
The second query matches customers only to paid orders. Ada has a match, while Linus and Grace remain with NULL in the order columns. Every customer appears in the result. In the sample, NULL means no paid order matched, rather than a total of zero. The schema also permits an actual paid order to have a NULL total.
SELECT c.id,c.name,o.total FROM customers c LEFT JOIN orders o ON o.customer_id=c.id AND o.status='paid' ORDER BY c.id;Swipe or scroll the table to see all columns.
| id | name | total |
|---|---|---|
| 1 | Ada | 40 |
| 2 | Linus | NULL |
| 3 | Grace | NULL |
What the comparison tells you
Try an empty orders table to check which customers remain. WHERE returns no rows, while ON keeps all three customers with NULL order totals. An order whose status is NULL also fails the paid condition, and its customer remains in the ON version. Both queries return no rows when the customers table is empty.
Place the condition according to the requested result. A WHERE filter can be appropriate when the report should show only customers with paid orders. To keep every customer, put the order condition in ON. Several paid orders can produce several rows for one customer. Check the required rows before comparing plans or adding indexes.
Check your understanding
Why do both Linus and Grace disappear when the paid-order condition is in WHERE?
Show the explanation
Linus has a pending match and fails the condition. Grace has NULL order fields and the condition is not true. WHERE removes both joined rows. In ON, neither has a qualifying match, so LEFT JOIN keeps each customer with NULL order fields.