✏️ Explanatory Question
Level: Hard — A subtle counting bug that silently inflates "zero-activity" numbers in reports.
LEFT JOIN so that customers with no orders still appear. But the report shows customers with 0 orders as having 1 order. Finance is confused why "inactive" customers show activity. Where's the bug?
| customer_id | name |
|---|---|
| 1 | Rumman |
| 2 | Krushna |
| 3 | Swetha |
| order_id | customer_id |
|---|---|
| 101 | 1 |
| 102 | 1 |
| 103 | 2 |
Note: Swetha (id 3) has no orders at all.
The cause: COUNT(*) counts rows, not orders. In a LEFT JOIN, Swetha still produces one row (with all order columns as NULL). So COUNT(*) counts that one "phantom" row as 1. The fix is COUNT(o.order_id), which counts only non-NULL order values — giving 0 for Swetha.
COUNT(*) (counts rows, including the NULL-filled placeholder row) and COUNT(column) (ignores NULLs) — a distinction that produces silently wrong reports.
SELECT c.name, COUNT(*) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;| name | order_count |
|---|---|
| Rumman | 2 |
| Krushna | 1 |
| Swetha | 1 ← wrong! |
| name | order_count |
|---|---|
| Rumman | 2 |
| Krushna | 1 |
| Swetha | 0 ✓ |
-- THE BUG: COUNT(*) counts the NULL-filled placeholder row as 1
SELECT c.name, COUNT(*) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
-- Swetha shows 1 (the phantom row)
-- THE FIX: COUNT a column from the RIGHT table (NULLs are ignored)
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
-- Swetha correctly shows 0
-- Same idea for SUM: wrap with COALESCE to avoid NULL totals
SELECT c.name, COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;COUNT() a non-nullable column from the right-hand (joined) table — typically its primary key. Never use COUNT(*) when you want to count matched rows only.
COUNT(DISTINCT o.product_id), which both ignores NULLs and removes duplicate products — combining two NULL-safe behaviours in one.