What value does the correlated subquery (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) produce for each customers row?
answer
- the inner query mentions the outer table
- one value per outer row, not per match
- the outer row's id is substituted each time
- SELECT-list subqueries add columns, never filter
- COUNT of an empty set is 0
basics
~20 sOne value per outer row: the inner SELECT is evaluated with that customer's id substituted, so each row shows that customer's own order count — and 0, not NULL, for a customer with no orders.
solid answer
~40 sThe subquery references `c.id`, a column of the enclosing query, so it cannot be evaluated on its own — conceptually it is evaluated once per row of `customers`, with that row's `id` plugged in, and the single value it yields becomes that row's `order_count`. The result therefore has exactly as many rows as `customers`: putting the subquery in the SELECT list adds a column, it never filters rows. A customer with no orders gets **0**, because `COUNT` over an empty set is 0 and an aggregate with no `GROUP BY` always returns exactly one row. Swap `COUNT(*)` for `MAX(o.amount)` and the same customer gets **NULL** instead, since `MAX` of nothing is NULL. If you removed `WHERE o.customer_id = c.id`, the subquery would be uncorrelated and would repeat one constant total on every row.
code
sql · 7 lines-- customers: (1,'Ada'), (2,'Ben'), (3,'Cleo')
-- orders: (10,1), (11,1), (12,2)
SELECT c.id,
c.name,
(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;
-- 1 Ada 2 | 2 Ben 1 | 3 Cleo 0go deeper
Be ready to read such a query aloud row by row and state the row count of the result and the value for a customer with no orders. Knowing that COUNT gives 0 while MAX gives NULL is the whole question.
Explain the substitution model precisely: the outer row binds the alias, the inner query yields one value, and an aggregate without GROUP BY always returns exactly one row. Contrast that with an empty result set evaluating to NULL.
Show that you know the SELECT-list form cannot filter, and that naively converting it to an inner join silently loses zero-count rows. Expect to say what you would check in the output to catch that regression.
Frame it as a readability and correctness convention: which shape your codebase standardises on for per-row aggregates, and why letting each author pick between correlated subqueries, joins and window aggregates produces reviews that argue about style instead of semantics.
## The two query blocks A subquery is a `SELECT` nested inside another statement. The enclosing block is the *outer* query and the nested one is the *inner* query. Each `FROM` item introduces a **range variable** — the alias `c` in `FROM customers c`, `o` in `FROM orders o` — and a qualified name like `c.id` means "the `id` column of the row currently bound to `c`". In `SELECT c.name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) FROM customers c` the inner block mentions `c.id`, a range variable owned by the outer block. That reference is what makes the subquery **correlated**: it has no meaning by itself, because `c` is only bound while the outer query is producing a row. An **uncorrelated** subquery mentions nothing from outside — `(SELECT COUNT(*) FROM orders)` is a self-contained query whose value is the same for every outer row. ## The row-by-row reading The semantics the standard gives you are: for each row the outer query produces, bind `c` to that row, evaluate the inner query with `c.id` replaced by that row's value, and use the single value it returns. ```sql -- customers: (1,'Ada'), (2,'Ben'), (3,'Cleo') -- orders: (10,1), (11,1), (12,2) -- Ada -> COUNT(*) of orders where customer_id = 1 -> 2 -- Ben -> COUNT(*) of orders where customer_id = 2 -> 1 -- Cleo -> COUNT(*) of orders where customer_id = 3 -> 0 ``` This is a *semantic* model, not a promise about execution — how an engine actually evaluates such a query is a query-planning matter, and it is free to compute the answer some entirely different way as long as the result matches. ## Why 0 and not NULL Two separate rules meet here, and mixing them up is the most common error. 1. A **scalar subquery that returns no rows at all** evaluates to NULL. 2. An **aggregate query without `GROUP BY` always returns exactly one row**, even when its input is empty. Because `SELECT COUNT(*) FROM orders o WHERE o.customer_id = 3` matches no `orders` rows but still has no `GROUP BY`, it returns one row containing `0`. So Cleo's column shows 0. Rule 2 also means `MAX`, `MIN`, `SUM` and `AVG` return one row containing **NULL** for an empty input — only `COUNT` returns a number. Add a `GROUP BY o.customer_id` inside and you change the answer for Cleo to NULL, because now there are no groups and therefore no rows at all, and rule 1 takes over. ## It adds a column; it does not filter Because the subquery sits in the SELECT list, the output has one row per `customers` row — three rows above, including Cleo with 0. Candidates routinely claim customers without orders disappear; that is what an `INNER JOIN` to `orders` would do, and it is exactly the trap when someone "optimises" this query into a join without switching to `LEFT JOIN`. To filter, the correlated predicate has to go into `WHERE`, for example `WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)`. ## The scalar contract A subquery used where a single value is expected must yield at most one row and one column. `(SELECT COUNT(*) …)` is safe by construction — an aggregate without `GROUP BY` can only produce one row. `(SELECT o.amount FROM orders o WHERE o.customer_id = c.id)` is not: it returns a value for customers with exactly one order, NULL for customers with none, and raises a runtime "more than one row returned by a subquery" error the moment some customer has two. ## Spotting correlation while reading Cover the outer query with your hand and ask whether the inner block would still run. If every column it names is resolvable from its own `FROM` list, it is uncorrelated and produces one constant. If it reaches out for `c.id`, it is correlated and produces a per-row answer. That one-line test is what an interviewer is checking when they hand you an unfamiliar query. ## Common mistakes - Reporting NULL for customers with no orders (confusing an empty aggregate with an empty result set). - Believing the query returns one row per matching order pair, as a join would. - Assuming the inner query runs once for the whole statement. - Forgetting that the same shape with `MAX` or `SUM` really does produce NULL, so downstream arithmetic needs `COALESCE`.
- How does the result change if the subquery uses MAX(o.amount) instead of COUNT(*)?Customers with no orders get NULL rather than 0. `MAX`, `MIN`, `SUM` and `AVG` over an empty input return one row containing NULL; `COUNT` is the one aggregate that returns a number. Wrap it in `COALESCE(..., 0)` if downstream arithmetic must not see NULL.
- What would the query return if you deleted the subquery's WHERE clause?It becomes uncorrelated: `(SELECT COUNT(*) FROM orders)` is a self-contained query with one constant value, so every customer row shows the same total order count for the whole table. Nothing ties the number to the customer any more.
- Does a correlated subquery in the SELECT list ever change how many rows come back?No. The SELECT list only shapes columns, so the output has exactly one row per row surviving the outer `FROM`/`WHERE`. To drop customers with no orders you need a predicate — `WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)` — or an inner join.
It behaves like a spreadsheet formula filled down a column: one formula, re-evaluated against each row's own cells, producing one answer per row.
saying these in an interview costs you the question
- Says a customer with no orders shows NULL for COUNT(*)
- Thinks the subquery drops customers that have no orders
- Claims the inner query is evaluated once for the whole statement
- Reads it as a join that multiplies rows per matching order
- Cannot say which column reference makes it correlated