CSE Roadmap

Core tier · about 3 hours · 5 parts

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.