What does a scalar subquery evaluate to when it selects no rows, and why is that risky?
answer
- no error is raised for an empty match
- the expression still has to have some value
- think about what a missing value does downstream
- in a predicate it becomes UNKNOWN, so rows vanish
- COALESCE supplies the default the engine will not
basics
~20 sA scalar subquery that matches no rows evaluates to NULL — no error, no default. That NULL then propagates through arithmetic and turns comparisons into UNKNOWN, so results quietly become NULL or rows quietly disappear from the output.
solid answer
~50 sReturning zero rows is legal for a scalar subquery: the expression is simply NULL. Nothing is raised, which makes it the dangerous direction of the single-value contract — too many rows fails loudly, too few fails silently. The NULL then behaves like any other NULL: `price - (SELECT amount FROM discounts WHERE code = 'X')` is NULL for every row when no such code exists, and `WHERE price > (SELECT ...)` evaluates to UNKNOWN for every row, so `WHERE` keeps none of them and the report comes back empty rather than wrong-looking. The defences are to supply an explicit default with `COALESCE((SELECT ...), 0)` when zero really is the right meaning, or to treat absence as a case worth detecting rather than papering over. Note that an aggregate subquery such as `(SELECT MAX(amount) FROM discounts WHERE code = 'X')` never takes the zero-row path at all.
code
sql · 4 lines-- no SUMMER24 row exists: the subquery is NULL, so net is NULL everywhere
SELECT p.sku,
p.price - (SELECT amount FROM discounts WHERE code = 'SUMMER24') AS net
FROM products p;go deeper
Know the two-part rule: no rows gives NULL, many rows gives an error. Being able to say which direction is silent already puts you ahead on this question.
Explain the propagation: NULL in arithmetic yields NULL, NULL in a comparison yields UNKNOWN, and WHERE keeps only TRUE — so the same missing row shows as NULL columns in one place and as an empty result in another.
Demonstrate the review habit of asking, for every scalar subquery, what an empty lookup and a duplicated lookup each do, and argue for COALESCE only where the default is a true statement about the domain.
Own the policy question: which absences a data product is allowed to silently default and which must surface as an alert, and how test fixtures are built so the missing-lookup path is exercised rather than assumed.
## The rule A scalar subquery is allowed to return **at most** one row, not exactly one. When it returns none, the expression evaluates to NULL. No exception is raised, no warning is issued, and no default is substituted. This is specified behaviour, not an engine quirk, and every conforming engine agrees on it. ```sql SELECT 10 + (SELECT bonus FROM bonuses WHERE code = 'NOPE'); -- NULL, because the subquery matched nothing ``` ## Why the silence is the problem The two ways a scalar subquery can break its contract are not symmetric: - **Too many rows** raises a cardinality violation. It is loud, it is immediate, and a test run notices it. - **Too few rows** produces NULL. It is quiet, and it flows onward into whatever expression contained it. So the failure that costs a team a day of debugging is usually the empty one. It surfaces as a column full of NULLs, a total that is NULL instead of a number, or a result set that is mysteriously empty. ## How the NULL travels Once the subquery is NULL, ordinary NULL propagation takes over, and it shows up in two distinct shapes. **In an expression**, arithmetic with NULL is NULL, and string concatenation with NULL is NULL in standard SQL. A single missing lookup poisons an entire computed column: ```sql SELECT sku, price - (SELECT amount FROM discounts WHERE code = 'SUMMER24') AS net FROM products; -- net is NULL for every product if no SUMMER24 row exists ``` That is visible, at least — a column of NULLs is hard to miss. **In a predicate**, the effect is worse because it removes evidence. `price > NULL` is UNKNOWN, and `WHERE` keeps only rows whose predicate is TRUE, so every row is discarded: ```sql SELECT * FROM products WHERE price > (SELECT price FROM products WHERE sku = 'MISSING-SKU'); -- zero rows: nothing matched, and nothing says why ``` An empty result looks exactly like "no data satisfied the condition", which is the answer a reader will accept without question. The same shape appears in `HAVING`, in a `CASE` `WHEN` test, and in a join condition. ## Defences **Supply a default, when a default is meaningful.** `COALESCE((SELECT amount FROM discounts WHERE code = 'SUMMER24'), 0)` says "no discount row means no discount", which is a real business statement. The important discipline is to write COALESCE because the default is correct, not because it makes the NULLs go away. **Distinguish absent from unknown.** If "no row" means something the caller should be told about, do not flatten it to a number. Return the value and a flag, or check for existence separately, so the caller can tell an absent lookup from a legitimate zero. **Prefer a shape that cannot go empty.** An aggregate subquery without `GROUP BY` always produces exactly one row, so the zero-row branch simply does not exist for it — the emptiness reappears as the aggregate's own value instead of as a missing row. Knowing which of the two shapes you wrote tells you which failure mode you have to reason about. **Test the empty case deliberately.** Because nothing is raised, only a test with a deliberately missing lookup key exercises this path. A fixture where every code resolves will never reveal the bug. ## The diagnostic habit When a query returns unexpected NULLs or an unexpectedly empty result, extract each scalar subquery and run it alone. Two questions answer almost every case: does it return zero rows, and does it return more than one? The first explains NULLs and vanishing rows; the second would already have raised an error. This is a thirty-second check that people skip because the outer query "looks right". ## Reading a query for both directions A useful review reflex on any query containing `(SELECT ...)` in a value position is to ask, out loud, what happens on an empty lookup and on a duplicated lookup. If the answer to the first is "the column goes NULL and nobody notices", the query needs either a COALESCE with a justified default or an explicit existence check. If the answer to the second is "it errors", the query needs a uniqueness guarantee it can rely on. Most scalar-subquery bugs in production are one of those two questions never having been asked.
- Operationally, why is the empty case worse than the many-rows case?Many rows raises an exception, so it is caught by the first run against real data and appears in logs with a message. Zero rows produces NULL, which flows into arithmetic and predicates and often ends up as a plausible-looking report. One failure interrupts you; the other convinces you.
- How does an empty scalar subquery in a WHERE comparison differ from one in the SELECT list?In the SELECT list you see the damage — a column of NULLs. In a WHERE comparison the predicate is UNKNOWN for every row, WHERE keeps only TRUE, and the result set is empty. The second hides the evidence, because an empty result is indistinguishable from a genuine no-match.
- Is wrapping every scalar subquery in COALESCE good practice?No. COALESCE is correct when the default states something true about the domain — no discount row means zero discount. Applied reflexively it converts a detectable absence into a fabricated value, which is how a missing exchange rate becomes a price of zero. Use it deliberately, per call site.
saying these in an interview costs you the question
- Expects an error when the subquery matches nothing
- Thinks an empty scalar subquery yields 0 or an empty string
- Assumes a NULL comparison keeps the row in the result
- Wraps every scalar subquery in COALESCE without justifying the default
- Cannot explain why an empty result set can come from a missing lookup