Thinking habit: Know when each step runs
SQL LEFT JOIN with WHERE: why rows disappear
The question: Show every customer with the number of paid orders they have. Customers with none should show 0.
customers
| customer_id | name |
|---|---|
| 1 | Asha |
| 2 | Ravi |
| 3 | Neha |
orders
| order_id | customer_id | status |
|---|---|---|
| 101 | 1 | paid |
| 102 | 1 | paid |
| 103 | 2 | pending |
This query looks right. Run it, decide if it is, and fix it.
Show the answer and why the first query is wrong
Why the usual answer is wrong
SELECT c.name, COUNT(o.order_id) AS paid_orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.status = 'paid' GROUP BY c.customer_id, c.name;
Ravi's only order is pending and Neha has none, so their rows have no 'paid' status. WHERE removes them after the join, and they vanish instead of showing 0.
The answer
SELECT c.name, COUNT(o.order_id) AS paid_orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'paid' GROUP BY c.customer_id, c.name;
Put the condition on the right-hand table in ON. Then it decides which orders join, and every customer is still kept.
Rule of thumb: in a LEFT JOIN, a WHERE on the right table's columns undoes the LEFT, unless it's an IS NULL check.
Practise more like this, on real business data
324 interview-style questions on 35 tables with the duplicates, NULLs and missing weeks interviewers test. Free.