Scalar Subquery

Hook: Interviewers love this one because it checks whether you know the 'one value only' rule — the tiny detail that saves you from big production bugs.

Question: What is a scalar subquery in SQL?

Answer: A scalar subquery is a subquery that returns exactly one column and at most one row, so SQL can use it like a single value inside a bigger query. If it returns no rows, the result is NULL. If it returns more than one row, most databases raise an error because a single value was expected.

Interview-Ready Answer: I’d say a scalar subquery is a subquery that behaves like one value. I can use it in places where SQL expects a single expression, like the SELECT list or a WHERE comparison. The important rule is that it must return one column and zero or one row; zero rows becomes NULL, and more than one row usually throws an error. In practice, I use it for small lookups or aggregate values, but if I need to combine many rows, I usually switch to a JOIN.

What it is

A scalar subquery is a subquery used in an expression position, meaning SQL expects one value there. 'Scalar' here means single-valued: one column, one row at most. A subquery that returns rows like a tiny table is a different thing entirely.

How it works under the hood

1. The outer query starts processing rows, or starts the query if the subquery is not correlated.
2. The database runs the inner query. A correlated subquery is one that references a column from the outer query, so its result can change for each outer row.
3. The engine checks the subquery cardinality, which means the number of rows returned.
4. If the subquery returns exactly one row, that single value is plugged into the outer expression.
5. If it returns zero rows, SQL substitutes NULL.
6. If it returns more than one row, most engines stop with an error because the expression is ambiguous.

Many optimizers can decorrelate a subquery, which means they rewrite it into a join or aggregate plan so it does not literally rerun the inner query for every row. But you should still think in the simple mental model first: 'ask for one value, then paste it into the outer query'.

When and why to use it

• Use it for a small lookup: 'Give me each employee and their department name'.
• Use it for a single computed value: 'Show rows above the average salary'.
• Use it when the logic is clearer as a value expression than as a join.
• Prefer it when you genuinely want one answer, not a list.

Scalar subquery vs join vs CTE

A quick rule: if you need one answer, scalar subquery is natural. If you need to attach data from another table to many rows, a join is often clearer and faster. If you need to break a complex query into named pieces, a CTE helps readability, but it does not replace the single-value rule.

Performance notes

An uncorrelated scalar subquery is often cheap because the engine can evaluate it once. A correlated scalar subquery can be much more expensive, because the naive plan is one inner lookup per outer row. For example, 10,000 outer rows with an indexed inner key may mean about 10,000 index probes, which is usually fine; 100,000 outer rows against an unindexed 1,000,000-row table can become painfully slow.

As a rough mental model, think O(1) for an uncorrelated subquery evaluated once, and O(N * lookup) for a correlated one. If the inner side has no useful index, that can drift toward O(N * M), which is why a query that looked elegant can suddenly take seconds or minutes. Interviewers like to hear that you would add an index on the correlated key, or rewrite to a join or grouped CTE if the plan is bad.

Important edge cases

• No rows: the result is NULL, not an error.
• More than one row: most databases raise an error like 'subquery returned more than one row'.
• Multiple columns: not allowed for a scalar subquery; it must be one column.
• NULL comparison: = NULL is never true; use IS NULL or COALESCE if you expect no match.
• ORDER BY without a guarantee: do not rely on 'first row' behavior unless the database explicitly supports it and you understand the implications.

One more practical tip: an aggregate like MAX or AVG is often used inside a scalar subquery because aggregates always return one row. That can be useful, but it can also hide bad data if you really expected uniqueness.

Real-World Story: Imagine a checkout service for an e-commerce site. Each order needs the customer's current discount tier, and the developer writes a scalar subquery to fetch that one discount rate while building the invoice. It works for months until a data bug creates two active discount rows for the same customer segment.

Now the scalar subquery returns more than one row, and the checkout query starts failing. Users see 500 errors at payment time, logs fill with the database message about a subquery returning multiple rows, and support tickets spike because carts cannot complete. The lesson is that scalar subqueries are only safe when the data model truly guarantees one answer, or when you deliberately aggregate or enforce uniqueness first.

Follow-up & Tricky Questions:

• What happens if the scalar subquery returns no rows? It returns NULL. That is why people often wrap it in COALESCE when they want a default value.
• What happens if it returns more than one row? Most databases throw an error because the outer expression cannot choose a single value.
• Is a correlated scalar subquery executed once per row? Conceptually yes, but the optimizer may rewrite it into a join or cache parts of it. Still, you should think about per-row cost when reading the query.
• When would you rewrite it as a join? Rewrite it when you need data from another table for many rows, especially if the correlated version is slow or duplicates are acceptable.
• Can I use a scalar subquery in the SELECT list only? No. You can also use it in WHERE, HAVING, ORDER BY, and many expression contexts wherever SQL expects one value.
• Does an aggregate inside a scalar subquery change the rule? It guarantees one row, which avoids the multi-row error, but it may hide duplicate-data problems if you were expecting uniqueness instead of a summary.
• Is = (SELECT ...) the same as IN (SELECT ...)? No. = expects a single scalar value, while IN checks membership in a set of values.
• Can I fix multi-row errors with LIMIT 1? You can in some databases, but it is usually a smell because it hides bad data and may return an arbitrary row unless you also define a deterministic order.

Common Mistakes:

• Thinking no rows is an error. Correction: no rows becomes NULL; the error is when there is more than one row.
• Using a scalar subquery for many related rows. Correction: if you are attaching columns from another table to many rows, a JOIN is often clearer.
• Forgetting the single-column rule. Correction: a scalar subquery must return one column, not a tuple of columns.
• Comparing the result to NULL with =. Correction: use IS NULL or COALESCE, because = NULL is not true.

Memory Hook: Think of a scalar subquery like asking one clerk for one number. If the clerk hands you zero slips, you get NULL; if they hand you two slips, the counter stops the line and throws an error.

Cheat Sheet:

• Scalar subquery = one column, at most one row.
• Zero rows => NULL.
• More than one row => error in most databases.
• Great for one-value lookups and aggregate comparisons.
• Correlated subqueries can be expensive if repeated per outer row.
• Use JOIN or a grouped CTE when you need set-based work.

Practice Tasks:

• Write a query that lists each employee and the average salary of their department using a scalar subquery.
• Write a query that finds employees earning more than the company-wide average salary.
• Change one department lookup to a missing key and verify that the result becomes NULL, then wrap it with COALESCE.