Thinking habit: Know what you're counting
COUNT(*) vs COUNT(column) in SQL: the LEFT JOIN trap
The question: Show every customer with the number of orders they placed. Customers with no orders should show 0.
customers
| customer_id | name |
|---|---|
| 1 | Asha |
| 2 | Ravi |
| 3 | Neha |
orders
| order_id | customer_id |
|---|---|
| 101 | 1 |
| 102 | 1 |
| 103 | 2 |
This query looks right. Run it, decide if it is, and fix it.
SELECT c.name, COUNT(*) AS orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id, c.name;
Show the answer and why the first query is wrong
Why the usual answer is wrong
SELECT c.name, COUNT(*) AS orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id, c.name;
Neha has no orders, but the LEFT JOIN still gives her one row (with NULL order columns). COUNT(*) counts that row, so she shows 1 order instead of 0.
The answer
SELECT c.name, COUNT(o.order_id) AS orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id, c.name;
COUNT(*) counts rows. COUNT(column) counts values that aren't NULL. After a LEFT JOIN, a customer with no orders has a row but no order_id, so count the order_id.
Grouping by customer_id as well as name keeps two customers with the same name apart.
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.