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 alias

How it works

  1. The inner query SELECT AVG(salary) FROM employees runs first and returns 63750.
  2. The outer query becomes, in effect, WHERE salary > 63750.
  3. Asha, Hana, Ben and Chen pass; the rest don't.
  4. 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.
  5. For a derived table, the inner query makes a small table (one total per customer) that the outer query averages.

Key points

Common mistakes

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.