What must a scalar subquery return, and what happens if it returns more than one row?
answer
- it stands where one value belongs
- the shape is a promise about rows
- zero rows is allowed; many rows is not
- checked while the query runs, not when parsed
- cardinality violation, SQLSTATE 21000
basics
~20 sA scalar subquery must produce exactly one column and at most one row, so it can stand where a single value is expected. Returning two or more rows is a cardinality error raised while the statement runs, not a syntax error caught beforehand.
solid answer
~50 sA scalar subquery is a subquery used in an expression position, so it must return **exactly one column and at most one row**. Zero rows is legal and the expression evaluates to NULL; two or more rows is a cardinality violation (SQLSTATE 21000), and PostgreSQL words it `more than one row returned by a subquery used as an expression`. The column count is checked statically when the statement is prepared, but the row count depends on the data, so the same query can pass in development and fail in production the day a duplicate appears. Make single-valuedness structural rather than hopeful: filter on a key the schema guarantees unique, or wrap the subquery in an aggregate such as `MAX(...)` so it can only yield one row. If the answer genuinely is a set, the predicate wanted `IN`, not `=`.
code
sql · 4 lines-- ERROR as soon as a second French customer exists
SELECT *
FROM orders
WHERE customer_id = (SELECT id FROM customers WHERE country = 'FR');go deeper
Be ready to state the shape rule in one sentence — one column, at most one row — and to say that too many rows is an error while no rows gives NULL.
Explain why the column count is caught when the statement is prepared but the row count only at execution, and show two ways to make the subquery single-valued by construction rather than by luck.
Show the diagnosis reflex: isolate the subquery, group the source by the lookup key, then choose between IN, a tighter unique predicate, or a deliberate aggregate — and argue against LIMIT 1 as a fix.
Frame it as an assumption that lives in the query rather than in the schema: uniqueness relied on by = should be declared where writes happen, so the failure appears at insert time instead of in a nightly job.
## What "scalar" means SQL classifies a nested query by the shape of its result: how many columns (its *degree*) and how many rows (its *cardinality*). A **scalar subquery** is the narrowest shape — one column, at most one row — and that shape is what lets it be written wherever a single value is legal: on either side of a comparison, inside arithmetic, as an item in the SELECT list, inside a `CASE` branch, in `ORDER BY`. The parentheses are required, and the whole parenthesised query behaves as if it were a literal. ```sql SELECT order_id, total FROM orders WHERE total > (SELECT AVG(total) FROM orders); ``` The engine evaluates the inner query, gets one number, and compares each row's `total` against it. ## The contract, case by case There are exactly three outcomes: - **One row, one column** — the normal case. The expression takes that value. - **Zero rows** — legal. The expression evaluates to NULL. Nothing is raised; this is the quiet failure mode, and it turns comparisons into UNKNOWN so rows silently drop out. - **Two or more rows** — an error. The standard calls it a *cardinality violation* and assigns it SQLSTATE 21000. PostgreSQL reports `more than one row returned by a subquery used as an expression`; MySQL reports that the subquery returns more than one row. SQLite is the documented exception: it ignores rows after the first rather than raising, which means a query that fails loudly elsewhere returns an arbitrary answer there. A fourth shape — more than one *column* — is not a runtime issue at all. `WHERE id = (SELECT id, name FROM users)` is rejected when the statement is prepared, because the two sides of `=` have different degree. Degree is static; cardinality is data-dependent. That asymmetry is the point of the question: the multi-row failure cannot be caught by parsing, only by running the statement against the data that happens to be there. ## Why it bites in production Almost every real occurrence follows the same story. Somebody wrote a lookup against a column they believed was unique — a code, a region, a status, a token — and it *was* unique for months. Then a second row arrived, and a query nobody had touched started failing. The query is not wrong syntactically and no plan changed; the assumption baked into `=` stopped holding. ```sql -- fine while exactly one office is in the EU SELECT * FROM orders WHERE office_id = (SELECT id FROM offices WHERE region = 'EU'); ``` ## Making it single-valued on purpose The fix is to decide what the query meant and encode that: - **Set semantics** — if several matches are all acceptable, the predicate is `IN`, not `=`. `WHERE office_id IN (SELECT id FROM offices WHERE region = 'EU')`. - **A genuinely unique key** — tighten the inner predicate so it selects on something the schema keeps unique (a primary key, a column with a UNIQUE constraint, a flag such as `is_headquarters = TRUE`). - **Deliberate collapse** — an aggregate without `GROUP BY` returns exactly one row by construction, so `(SELECT MAX(created_at) FROM audit_log)` can never trip the error. Use this when "the largest", "the latest" or "the only one, if any" is honestly what you want. ## The anti-fix Adding `LIMIT 1` (or the standard `FETCH FIRST 1 ROW ONLY`) silences the error, and that is exactly why it is dangerous: it converts a loud, correct complaint into a quiet, arbitrary answer. Without `ORDER BY`, no engine promises which row you get, and ties under an `ORDER BY` are still broken arbitrarily. It is defensible only when you can state the ordering that makes the choice deterministic and you actually want one of the matches. ## Reading the error When this error appears, run the subquery on its own. If it returns several rows, you have both the diagnosis and the data to reason about — group the source table by the lookup key and look for counts above one. The error message names the query, not the offending key, so the isolation step is not optional. ## The mirror image Remember that the two failure modes are asymmetric in how kindly they treat you. Too many rows is an exception: loud, immediate, impossible to ignore. Too few rows is NULL: silent, propagating through arithmetic and comparisons, and perfectly capable of producing a report of zero rows that somebody believes. When you audit a query for this contract, check both directions.
- Does adding LIMIT 1 inside the subquery fix the problem?It stops the error, but it does not fix the query. Without an ORDER BY no engine promises which row you get, so a loud, correct failure becomes a quiet, arbitrary answer — and ties under an ORDER BY are still broken arbitrarily. It is only defensible when you can state the ordering that makes the pick deterministic and one match really is what you want.
- Why is a multi-column subquery rejected earlier than a multi-row one?Degree — the number of columns — is fixed by the subquery's SELECT list, so the engine knows it while preparing the statement and rejects `id = (SELECT id, name FROM users)` immediately. Cardinality depends on the rows present at execution time, so it can only be detected once the second row materialises.
- Besides a WHERE comparison, where else does the single-value contract apply?Everywhere an expression is legal: the SELECT list, arithmetic, a CASE branch, ORDER BY, a comparison inside HAVING. The rule travels with the position, not with the clause, so the same subquery that is fine on the right of IN becomes illegal the moment it is used as a value.
A scalar subquery is a fill-in-the-blank on a form. The blank holds one value, so handing it a list is a size mismatch — and the clerk only notices when they reach for it.
saying these in an interview costs you the question
- Says the parser rejects a multi-row scalar subquery before execution
- Assumes the engine just picks the first row
- Thinks zero rows raises an error rather than yielding NULL
- Adds LIMIT 1 with no ORDER BY and calls it fixed
- Believes a scalar subquery may return several columns