Part 2 of 5 · 1 min read
Scalar and nested subqueries
A query used as a single value, or as a list another query checks against.
Think of a form that asks for "the average price of all books." That one number then gets used elsewhere on the same form, computed first and treated as a single fact from then on.
In plain words: a scalar subquery returns exactly one value, usable anywhere a single value is expected, such as in a comparison. A nested subquery, more generally, returns a set of values that an outer query checks a column against, often with IN. Both run independently of the outer query, computed once before the outer query uses the result.
A worked example. Finding books priced above the average price of all books.
The key lines.
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);
Scalar subquery, nested subquery
A scalar subquery returns exactly one value. A nested subquery returns a set of values, usually checked against with IN. Both are evaluated independently of the outer query.
Example: (SELECT AVG(price) FROM books) is a scalar subquery; (SELECT customer_id FROM orders WHERE placed_on >= '2026-01-01') is a nested subquery.