Multiple Row Subquery

What Is a Multiple-Row Subquery?

A multiple-row subquery is a subquery whose result set may legitimately contain more than one row. Because plain comparison operators (=, >, etc.) demand a single value, a multi-row subquery must be paired with an operator built for sets: IN, NOT IN, ANY, ALL, or wrapped in EXISTS / NOT EXISTS.

Sample data used throughout this chapter:

employees

departments

IN — the Most Common Pairing

sql
SELECT name
FROM employees
WHERE department IN (SELECT name FROM departments WHERE budget > 150000);

Departments with budget > 150000: Engineering (300000) and Sales (200000). The subquery returns 2 rows — exactly what IN is designed for.

Result: Alice, Bob, Carol, Dave, Eve, Frank (everyone in Engineering or Sales — 6 rows). Grace and Heidi (Marketing) are excluded since Marketing's budget is exactly 150000, not greater than it.

Why = Would Break Here

sql
-- WRONG — will raise a runtime error
SELECT name
FROM employees
WHERE department = (SELECT name FROM departments WHERE budget > 150000);

Because the inner query returns 2 rows ('Engineering', 'Sales'), this raises the same "more than one row" runtime error described in 17.2. = can never safely stand in for IN.

The Full Toolkit for Multi-Row Subqueries

Edge Cases

• If the multi-row subquery returns zero rows, IN (...) simply matches nothing (empty result) — no error.
• If it returns duplicate values, IN doesn't care — duplicates don't cause double-counting of outer rows, since IN is a membership test, not a join.
• IN subqueries (barring row-value syntax) must still return a single column — a subquery returning multiple columns is a different error than "multiple rows."

Key Takeaways / Q&A

Q: Is `IN (subquery)` the same as `= ANY (subquery)`? A: Yes — IN and = ANY are equivalent for multi-row subqueries.

Q: Can `NOT IN` with a multi-row subquery ever behave unexpectedly? A: Yes, dangerously so — if any returned row is NULL, NOT IN can silently return zero rows for the entire outer query. Prefer NOT EXISTS when NULLs are possible (17.6).

Q: Does a multiple-row subquery always come from a GROUP BY? A: No — any subquery without a uniqueness guarantee (no aggregate, no unique-column filter) can return multiple rows, including a simple SELECT column FROM table WHERE ... against a non-unique predicate.