After a LEFT JOIN, why does COUNT(*) return 1 for customers with no orders?
answer
- the unmatched customer is still in the result
- the join pads the right side
- rows exist even when values do not
- choose an expression that is NULL there
- count the inner table's key
basics
~20 sA LEFT JOIN keeps one manufactured row per unmatched customer, with every orders column NULL, and COUNT(*) counts that row. Count a NOT NULL column from the joined side instead — COUNT(o.order_id) — which returns 0.
solid answer
~40 sAn outer join preserves every row of the left table. When a customer has no matching order, the join still emits one row for that customer with all `orders` columns set to NULL. `COUNT(*)` counts rows, and that manufactured row is a row, so the group's count is 1. The fix is to count something that is NULL exactly in the manufactured row: a column from the **inner** side that can never be NULL in its own table, normally its primary key — `COUNT(o.order_id)`. That gives 0 for customers with no orders and the true number otherwise. Two traps come with it: counting a *nullable* column of the joined table (`COUNT(o.shipped_at)`) undercounts real orders, and counting a left-side column (`COUNT(c.customer_id)`) gives 1 again, because the preserved side is never NULL.
code
sql · 5 lines-- WRONG: every customer reports at least 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;go deeper
Remember the symptom and the fix: after a LEFT JOIN, COUNT(*) gives 1 for a customer with no orders, and COUNT(o.order_id) gives 0. Be able to point at the padded NULL row as the cause.
Explain the mechanism — the outer join emits one row with the right side NULLed, and COUNT(*) counts rows regardless of content — and justify why you count the joined table's primary key rather than any of its columns.
Show the review instinct: this bug produces a plausible number, not an error, so name the sanity checks you run on any grouped count over a join, and mention the sibling trap of filtering the optional side in WHERE.
Treat it as a metric-correctness policy question: aggregates over outer joins should be reviewed against a stated definition and covered by a reconciliation check, because silently-wrong dashboards outlive the person who wrote the query.
## 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.
- Which column of the joined table should you count, and why does the choice matter?One that cannot be NULL in that table itself — normally its primary key. A nullable column such as `shipped_at` also gives 0 for unmatched customers, but it silently drops every real order that has no shipping date, turning your row count into an unintended conditional count.
- Why doesn't COUNT(c.customer_id) work instead?The left table is the preserved side, so its columns are never NULL in the padded row. Counting them behaves exactly like `COUNT(*)` and reports 1 for a customer with no orders. Only the optional side carries the NULL that distinguishes a real match.
- Would switching to an INNER JOIN fix the count?It removes the wrong number by removing the customer: an inner join drops rows with no match, so zero-order customers disappear from the report entirely. If the report is meant to list every customer, keep the outer join and count the inner side's key.
- Says the LEFT JOIN dropped the customer with no orders
- Thinks COUNT(*) skips rows whose joined columns are all NULL
- Counts the preserved table's key and expects zero
- Switches to INNER JOIN and calls the missing rows correct
- Writes COUNT(o.*) expecting it to count matched rows
context
- Q-28277What do COUNT(*) and COUNT(city) return for a 10-row table where 3 rows have NULL city?level: juniorimportance: must knowfreq 88%
- Q-28460What does LEFT OUTER JOIN return for left rows that have no match on the right?level: juniorimportance: must knowfreq 88%