Single Row Subquery

What Is a Single-Row Subquery?

A single-row subquery is a subquery you intend and expect to return at most one row, so it can be safely used with single-row comparison operators: =, <, >, <=, >=, <>. It's the WHERE-clause cousin of the scalar subquery (17.1) — same shape requirement, framed from the "is it safe to compare against?" angle.

Sample data used throughout this chapter:

employees

id	name	department	salary	manager_id
1	Alice	Engineering	95000	NULL
2	Bob	Engineering	72000	1
3	Carol	Engineering	68000	1
4	Dave	Sales	60000	5
5	Eve	Sales	88000	NULL
6	Frank	Sales	55000	5
7	Grace	Marketing	70000	NULL
8	Heidi	Marketing	62000	7

departments

id	name	budget
1	Engineering	300000
2	Sales	200000
3	Marketing	150000
4	HR	100000

A Correct Example

sql
SELECT name, salary
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE name = 'Eve');

name = 'Eve' matches exactly one row, so the subquery safely returns one value: 88000. Result:

name	salary
Alice	95000

Only Alice earns more than Eve.

The Runtime-Error Gotcha

This pattern is a trap the moment the "obviously unique" filter isn't actually unique:

sql
SELECT name
FROM employees
WHERE department = (SELECT department FROM employees WHERE manager_id = 5);

manager_id = 5 matches two rows — Dave and Frank both report to Eve (employee id 5) — so the subquery returns two rows. This raises a runtime error: more than one row returned by a subquery used as an expression.

This is important: it is NOT a silent wrong answer. The query fails outright, loudly, the moment it runs against data where the assumption ("only one row will match") turns out to be false. It's a genuine gotcha because the exact same query text can succeed on one day's data and fail on the next, purely because the underlying data changed shape — nothing about the SQL itself changed.

Note also: even if both matching rows happened to have the identical value (say both were 'Sales'), most engines (including PostgreSQL) still raise the error — the check is on row count, not on whether the values are distinct.

How to Avoid It

Add more filtering conditions to guarantee uniqueness (e.g., filter by a primary key or a column with a UNIQUE constraint).
Force one row with an aggregate: (SELECT MIN(department) FROM employees WHERE manager_id = 5).
Or accept the subquery is genuinely multi-row and switch to IN / ANY / ALL / EXISTS (17.3, 17.7, 17.8).

Key Takeaways / Q&A

Q: Is "subquery returns more than one row" a compile-time or runtime error? A: Runtime — the query is syntactically valid; the engine only discovers the violation while executing it against actual data.

Q: Does `=` ever tolerate multiple rows? A: No. =, <, >, <=, >=, <> all require single-row subqueries; use IN / ANY / ALL for a subquery that legitimately returns many rows.

Q: Is a single-row subquery always a scalar subquery? A: Effectively yes — one row, one column — but "single-row" frames the WHERE-clause safety guarantee, while "scalar" (17.1) frames it as a value used as data.

Edge case: a single-row subquery that returns zero rows does NOT error — the comparison becomes x > NULL, which is UNKNOWN, and the outer row is simply excluded. Zero rows is safe; two-or-more rows is fatal.

Mock Test

Single Row Subquery - Quick Test
8 questions on Single Row Subquery.
8 questions · 8 min · Medium
Start Mock Test

Single Row Subquery