Subqueries: a query inside a query
A subquery is a SELECT inside another query, in brackets: it can return one value (to compare with), a list (for IN), or a whole table (to select FROM).
Why it matters
"Who earns more than the average?" needs the average first, then a comparison. "What is the average customer's total spend?" needs a total per customer first, then an average of those. Each needs two steps, and a subquery puts both steps into one query.
The idea
The database runs the inner query and uses its result in the outer one. There are three shapes.
A scalar subquery returns one value — (SELECT AVG(salary) FROM employees) — and goes anywhere a single value can.
A list subquery returns one column of many rows and goes after IN.
A table subquery (a derived table) goes in FROM, needs an alias, and is used like any other table — which is how you aggregate an aggregate.
Asking a friend to look something up while you wait. "What is the average salary?" — they come back with 63750 — "OK, then show me everyone above that." The inner question is answered first and its answer is slotted into the outer one.
Your friend answers once. A correlated subquery (next lesson) is different: it is asked again for every outer row, with that row's values plugged in.
Syntax
WHERE column > (SELECT one_value FROM ...)
WHERE column IN (SELECT one_column FROM ...)
FROM (SELECT ... ) AS aliasHow it works
- The inner query
SELECT AVG(salary) FROM employeesruns first and returns 63750. - The outer query becomes, in effect,
WHERE salary > 63750. - Asha, Hana, Ben and Chen pass; the rest don't.
- For IN, the inner query returns a list of customer ids (2, 1, 5), and the outer query keeps customers whose id is in the list.
- For a derived table, the inner query makes a small table (one total per customer) that the outer query averages.
Key points
- A subquery is a query in brackets, run to feed the outer query.
- Scalar subquery: exactly one value. If it returns more than one row, the query fails.
- IN subquery: one column, any number of rows.
- Derived table in FROM: give it an alias. Use it to aggregate an aggregate or to filter on a calculated column.
- Most subqueries can also be written as a join or a CTE; pick whichever reads most clearly.
Common mistakes
- A scalar subquery that returns several rows ("more than one row returned by a subquery").
- Forgetting the alias on a derived table (required in PostgreSQL before version 16, MySQL and SQL Server).
- Nesting subqueries three deep. Rewrite it with CTEs so it reads top to bottom.
Not true: Subquery in FROM: an alias is optional in PostgreSQL 15, MySQL and SQL Server.
In fact: PostgreSQL before version 16, MySQL and SQL Server all require an alias for a subquery in FROM. Give it one every time.
In the interview
A subquery is a nested SELECT whose result the outer query uses: as a single value for comparison, as a list for IN, or as a derived table in FROM. I use derived tables when I need to aggregate in two stages, like averaging per-customer totals. Once nesting gets deeper than one level, I switch to CTEs, so the logic reads top to bottom.
Part of Unit 4: Subqueries, CTEs and set operations in the free SQL course.