After a LEFT JOIN, why does COUNT(*) return 1 for customers with no orders?

## What the join actually produces A `LEFT OUTER JOIN` guarantees that every row of the left (preserved) table appears in the result at least once. If a left row has no join partner, the engine emits one row for it anyway and fills every column of the right table with NULL. Those NULLs are not data — they are a marker meaning "no match existed". So for a customer with no orders, the join output is one row: real customer columns, NULL order columns. ## Why COUNT(*) says 1 `COUNT(*)` counts rows in the group and inspects no column. The manufactured row is a row. Hence: ```sql -- WRONG: customers with no orders report 1 SELECT c.customer_id, COUNT(*) AS order_count FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id; ``` Every customer gets at least 1, and the report quietly claims that people who never bought anything placed one order. Nothing errors; the number is simply wrong. ## The fix: count the inner side `COUNT(<expression>)` counts only rows where the expression is not NULL. Pick an expression that is NULL exactly in the manufactured row — any column of the right table works in principle, but the safe choice is one that can never be NULL in the right table itself, i.e. its primary key: ```sql -- RIGHT: customers with no orders report 0 SELECT c.customer_id, COUNT(o.order_id) AS order_count FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id; ``` Now the padded row contributes nothing and the count is 0, while matched customers count their real orders. ## Why the column choice matters Two wrong picks are common. 1. **A nullable column of the right table.** `COUNT(o.shipped_at)` returns 0 for the unmatched customer — correct — but it also ignores every real order that has not shipped yet. You have accidentally written a conditional count. If that is what you meant, say so explicitly; if not, use the key. 2. **A column of the left table.** `COUNT(c.customer_id)` is back to 1: the preserved side is never NULL, so this behaves like `COUNT(*)`. There is no `COUNT(o.*)` in standard SQL — the star form takes no qualifier of that kind in most engines, so do not reach for it. ## The group still exists — that is the point Beginners sometimes conclude that the customer "was dropped" or that they should switch to an inner join. The opposite is true: the outer join is what keeps zero-activity customers in the report at all. An `INNER JOIN` would remove them entirely, and the report would silently lose rows instead of showing wrong ones. Keep the outer join and fix the aggregate. ## Related trap in the same query Adding a predicate on the right table in the `WHERE` clause — `WHERE o.status = 'PAID'` — discards the manufactured rows, because NULL fails the comparison, and your zero-order customers vanish again. Predicates on the optional side belong in the join's `ON` clause; that is a distinct topic, but it is the second half of the same bug in practice, and it is worth naming so the interviewer knows you have hit it. ## Sanity checks Before trusting a grouped count over a join, run two cheap checks: total the per-group counts and compare with the plain `SELECT COUNT(*) FROM orders` for the same filter, and eyeball a customer you know has no activity. A one-minute check catches both the "everyone has at least one" symptom and the reverse, missing rows. ## How to answer it Say what the join emits (one padded row of NULLs), say what `COUNT(*)` counts (rows, without looking at columns), then give the fix and justify the column you chose. Volunteering the nullable-column caveat is what separates a memorised trick from an understood rule.