Subqueries

A query inside a query.

Queries within queries

A subquery is a SELECT nested in another. A scalar subquery returns one value you can compare against — "books priced above average":

SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);

A subquery can also feed an IN (...) with a whole column of values — "books by UK authors":

SELECT title FROM books
WHERE author_id IN (SELECT id FROM authors WHERE country = 'UK');

The inner query runs once and collapses to a single value, so by the time the outer query runs it is comparing against a plain number. Four of the ten books sit above 16.598.

The shape decides the operator. A subquery that returns many rows cannot sit beside a plain > — that mismatch is the most common subquery error.