Queries inside queries

Use one query's answer inside another, as a single value, a list, or a whole table.

Some questions need two steps. “Which deals are bigger than average?” means working out the average first, then comparing every deal to it. A subquery is a query in parentheses that runs inside another query and hands back its answer.

You’ve already used one: lesson 18’s percent-of-total divided by (SELECT SUM(amount) FROM ...).

Three shapes, three places

One value: compare every row to a calculated number

SELECT name, amount
FROM opportunities
WHERE amount = (SELECT MAX(amount) FROM opportunities);

The inner query runs first and returns 150,000. The outer query then keeps every deal worth exactly that. Four deals tie, and all four come back, which is something ORDER BY amount DESC LIMIT 1 would miss.

A one-value subquery also works in SELECT, where it repeats on every row:

SELECT name,
       amount,
       (SELECT MAX(amount) FROM opportunities) - amount AS gap_to_biggest
FROM opportunities
WHERE stage = 'Proposal';

Trap: a one-value subquery must return exactly one value. If it returns several rows, SQL Server and PostgreSQL stop with an error. SQLite, the sandbox, silently uses the first row and gives a wrong answer with no warning. Use an aggregate like MAX or AVG so there’s only ever one.

A list: filter by another table

Which deals belong to Healthcare accounts? The industry lives in accounts, but the deals live in opportunities:

SELECT name, amount
FROM opportunities
WHERE account_id IN (SELECT account_id
                     FROM accounts
                     WHERE industry = 'Healthcare');

The inner query returns a list of account IDs, and IN keeps the deals that match one of them: 15 deals worth $592,000. A join (lesson 14) gets the same answer. The subquery reads closer to the question when you only need columns from one table.

NOT IN has a nasty trap with NULLs. Lesson 26 covers it and the safer alternative.

A whole table: query a result

A subquery in FROM acts like a temporary table. This is how you aggregate twice, for example the average open pipeline per rep:

SELECT AVG(pipeline) AS avg_rep_pipeline
FROM (SELECT owner_id, SUM(amount) AS pipeline
      FROM opportunities
      WHERE closed_at IS NULL
      GROUP BY owner_id) AS rep_totals;

The inner query makes one row per rep. The outer query averages those rows: $206,500. You can’t write AVG(SUM(amount)) in one step, because SQL won’t put one aggregate directly inside another (Aggregating an aggregate).

Always give a FROM subquery an alias, like rep_totals. Most databases require it.

Correlated subqueries: one per row

A subquery can refer to the outer row. Then it runs again for every row:

SELECT o.name, o.amount
FROM opportunities AS o
WHERE o.amount > 2 * (SELECT AVG(x.amount)
                      FROM opportunities AS x
                      WHERE x.account_id = o.account_id);

For each deal, the inner query averages that deal’s account, then the outer query keeps deals worth more than double it. The x.account_id = o.account_id line is what ties the inner query to the current outer row.

They’re powerful but can be slow on big tables, because the inner query reruns for every row. When you see one getting complicated, the next lesson’s CTEs are usually clearer.

Common mistakes

Dialect notes

Snowflake notes

Snowflake errors when a one-value subquery returns more than one row, and supports correlated subqueries in WHERE and SELECT. Some correlated forms aren’t supported and fail with an “unsupported subquery type” error. When that happens, rewrite it as a join or a CTE.

More in the field guide: Subquery and Aggregating an aggregate.

Try it

Which lost deals were worth more than the average lost deal? These are the big ones that got away. Return the name and amount, largest first. Make sure the average only covers lost deals too: averaging every deal gives a different answer.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson