What is a scalar subquery, where can it appear in a SQL statement, and what happens if it returns more than one row?

How to think about it

“Scalar” just means a single value. A scalar subquery reduces to exactly one column and one row, so it slots in anywhere you’d otherwise write a literal number or string — the SELECT list, a WHERE predicate, a HAVING clause, even a JOIN ON. The interesting parts are where it can appear and what breaks it.

A worked example — scalar subquery in SELECT and WHERE

Here the same AVG(salary) subquery does double duty: it attaches the company average to every row and filters to above-average earners:

SELECT name, dept_id, salary,
       ROUND((SELECT AVG(salary) FROM employees)) AS company_avg,
       salary - ROUND((SELECT AVG(salary) FROM employees)) AS vs_avg
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

name	dept_id	salary	company_avg	vs_avg
Dave	2	120000	94167.0	25833.0
Eve	2	110000	94167.0	15833.0
Alice	1	95000	94167.0	833.0

Three above-average earners; company_avg is the same scalar (94167) repeated on each row, and vs_avg is each salary’s distance from it. Notice the subquery is written three times here — twice in SELECT, once in WHERE — and the engine evaluates it on each reference. A CTE or window function computes it once. (ROUND returns a float in SQLite, hence the .0.)

The “more than one row” runtime error

The database can’t know at parse time whether a correlated subquery will return one row or many — it depends on the data. If it returns more than one at runtime:

ERROR: more than one row returned by a subquery used as an expression

This only fires when a match exists, so a query that’s fine in staging can blow up in production as the data grows. LIMIT 1 is the usual patch, but it silently discards ties — which may not be what you want. For per-row lookups like “top earner per department,” a window function is cleaner and lets the optimiser scan once:

SELECT department_id, name, salary
FROM (
  SELECT department_id, name, salary,
         RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rk
  FROM employees
) ranked
WHERE rk = 1;