Why prefer a derived table in FROM over several scalar subqueries in the SELECT list?
answer
- one expression, one column, no reuse
- three aggregates means three copies
- the outer WHERE cannot see them
- a named table exposes all columns at once
basics
~20 sEach select-list subquery yields one column and is usable nowhere else in the statement. A derived table computes the group once, exposes several columns at once, and the outer query can filter, join and sort on them by name.
solid answer
~40 sA subquery in the select list is a single expression producing a single column, so three related aggregates mean three separate subqueries repeating the same filter, and nothing else in the statement can reference their results. `WHERE` cannot see a select-list alias, so filtering on the computed value means repeating the whole subquery or wrapping the query in another level. Pulling the aggregation into a derived table in `FROM` computes it once per group, returns all the columns together, and gives them names that the outer `WHERE`, `ORDER BY` and further joins can use. The cost is shape: you join to it, usually with `LEFT JOIN` so rows with no matching group survive, and then `COALESCE` the missing values — where a `COUNT` subquery in the select list would simply have returned 0.
code
sql · 5 linesSELECT c.id, c.name,
(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count,
(SELECT SUM(o.total) FROM orders o WHERE o.customer_id = c.id) AS lifetime_value,
(SELECT MAX(o.placed_at) FROM orders o WHERE o.customer_id = c.id) AS last_order_at
FROM customers c;go deeper
Know that a subquery in the select list gives one column, and that computing several related values usually belongs in a subquery placed in FROM instead.
Be able to perform the rewrite live: group the inner query by the correlation key, LEFT JOIN on it, and explain why COALESCE is needed to keep the counts reading as 0 rather than NULL.
Demonstrate the judgment call — when the extra join block is worth it, when a single annotation column reads better, and how to spot the rewrite silently dropping rows that had no matching group.
Frame it as a maintainability standard: repeated near-identical subqueries in a select list are duplicated business logic, and consolidating them into named intermediate result sets is what keeps analytical SQL reviewable.
## The two shapes Suppose each customer needs three numbers attached: how many orders they placed, what they spent in total, and when they last ordered. Both shapes below answer the question; they differ in where the computation lives. The select-list shape puts one subquery per number: ```sql SELECT c.id, c.name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count, (SELECT SUM(o.total) FROM orders o WHERE o.customer_id = c.id) AS lifetime_value, (SELECT MAX(o.placed_at) FROM orders o WHERE o.customer_id = c.id) AS last_order_at FROM customers c; ``` The FROM shape computes all three together and joins: ```sql SELECT c.id, c.name, COALESCE(o.order_count, 0) AS order_count, COALESCE(o.lifetime_value, 0) AS lifetime_value, o.last_order_at FROM customers c LEFT JOIN ( SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS lifetime_value, MAX(placed_at) AS last_order_at FROM orders GROUP BY customer_id ) AS o ON o.customer_id = c.id; ``` ## What the select-list position costs you **One column per subquery.** A select-list subquery occupies the slot of a column expression, so it can deliver exactly one value. Three numbers require three subqueries, each repeating the same `WHERE o.customer_id = c.id`. When the filter changes, it changes in three places, and a missed one is a silent inconsistency rather than an error. **Nothing else can reference the result.** The alias you give the column belongs to the select list, and the select list is not visible to `WHERE` — the standard evaluates the filter before the select list exists. So `WHERE lifetime_value > 1000` will not compile. Your options are to paste the whole subquery into `WHERE` a second time, or to wrap the entire query in another derived table and filter one level up. Both are worse than having put the computation in `FROM` to begin with. **It resists reuse.** If a later join needs the same aggregate, or `ORDER BY` needs it, the expression must be repeated or the query wrapped again. Named columns of a derived table have none of that friction. ## What the FROM position costs you **Join shape and NULLs.** The derived table only contains rows for groups that exist. A customer with no orders has no row in it, so an `INNER JOIN` would silently drop that customer — a classic bug in this rewrite — and a `LEFT JOIN` gives NULL in every column from it. The select-list version behaves differently for exactly this case: `(SELECT COUNT(*) …)` over zero matching rows returns 0, not NULL. Reproducing the original numbers therefore needs `COALESCE(o.order_count, 0)`. Deciding whether 0 or NULL is the right answer is a modelling question, and it is the single most common source of a wrong rewrite. **More visual weight.** A joined block of five lines sits between the reader and the select list. For one small annotation column, a single `(SELECT …)` next to the column it annotates genuinely reads better. **It aggregates everything.** The derived table groups the whole `orders` table, not just the customers you selected. When the outer query is highly selective, that is work you did not need; when it is not selective, computing the groups once is the cheaper shape. How an engine actually schedules either version is a planning matter, but the language-level difference — one aggregation over all keys versus one lookup per output row — is what you should be able to describe. ## Choosing Use the select list when you want one extra value, on a modest result set, and nothing else in the statement needs it. Move to a derived table in `FROM` the moment any of these appear: a second aggregate over the same rows, a need to filter or sort by the computed value, or a subquery whose body has grown past a couple of lines. The rewrite is mechanical — group the inner query by the correlation key, join on that key, and decide deliberately what a missing group should read as. ## The tell in an interview A candidate who says "pull it into a derived table" and stops has half the answer. The half that matters is naming the two behavioural differences the rewrite introduces: rows with no matching group must be preserved with `LEFT JOIN`, and their aggregate values change from 0 to NULL unless you put them back.
- What changes in the result for a customer with no orders?The select-list `COUNT(*)` subquery returns 0, because counting an empty set of rows still produces a number. The `LEFT JOIN` to the derived table finds no matching group, so every column taken from it is NULL. Reproducing the original output therefore needs `COALESCE(o.order_count, 0)`, and using `INNER JOIN` instead would drop the customer entirely.
- When is a select-list subquery still the better choice?When you need one value, the outer result set is small, and readability wins: a single `(SELECT …)` beside the column it annotates is easier to read than an extra joined block. It also stays natural when the value comes from a lookup that would otherwise complicate an already busy join graph.
- Can you filter on a value produced by a select-list subquery?Not by its alias. `WHERE` is defined to be evaluated before the select list, so the name does not exist yet. You either repeat the whole subquery inside `WHERE`, or wrap the query in a derived table and filter at the outer level. Computing it in a derived table from the start avoids the choice.
saying these in an interview costs you the question
- Claims the two shapes always return identical numbers
- Filters on a select-list subquery's alias in WHERE
- Uses INNER JOIN to the aggregate and silently drops rows
- Repeats the same subquery body for each aggregate needed
- Thinks a derived table can only return a single column