Scalar Subquery

What Is a Scalar Subquery?

A scalar subquery is a subquery that is guaranteed to return exactly one row and exactly one column — a single value. Because it collapses to one value, SQL lets you use it anywhere a literal value, column reference, or expression would be legal: in the SELECT list, in a WHERE comparison, in an ORDER BY, even as a default value.

Sample data used throughout this chapter:

employees

departments

Basic Example

sql
SELECT name, salary, (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;

Result (the company average is 71250.00, the same for every row since this particular subquery is uncorrelated):

Using It in a Comparison

sql
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Result: Alice (95000), Bob (72000), Eve (88000), Grace (70000) — everyone above 71250.

What Happens If It Doesn't Return Exactly One Value?

• If the inner query returns zero rows, a scalar subquery evaluates to NULL (not an error) — e.g. (SELECT salary FROM employees WHERE name = 'Zack') yields NULL, and any comparison against it (> NULL) also evaluates to UNKNOWN, so rows are silently excluded rather than erroring.
• If it returns more than one row, most engines raise a runtime error ("more than one row returned by a subquery used as an expression") — see 17.2 for the full gotcha.
• It must also return exactly one column — (SELECT name, salary FROM ...) used where a scalar is expected is a syntax/type error.

Aggregates Make This Safe

Aggregate functions (AVG, COUNT, SUM, MAX, MIN) always collapse to a single row when there's no GROUP BY, which is why scalar subqueries are almost always built around an aggregate with no GROUP BY, or a query filtered down to a unique key (like a primary key lookup).

Key Takeaways / Q&A

Q: Can a scalar subquery appear in an ORDER BY? A: Yes — anywhere a single-value expression is legal.

Q: What's the difference between a scalar subquery and a single-row subquery? A: They're closely related; "scalar" emphasizes it returns one value used as data (e.g., in SELECT), while "single-row" (17.2) emphasizes the guarantee needed for a WHERE comparison. In practice they're the same underlying shape.

Q: Is a scalar subquery in SELECT correlated or uncorrelated? A: It can be either — (SELECT AVG(salary) FROM employees) above is uncorrelated (same value for every row); a correlated version is shown in 17.9.

Edge case: a scalar subquery returning zero rows gives NULL, not an error — easy to mistake for "no such employee" logic breaking silently rather than failing loudly.