Subqueries in SQL#

📊 Analyze Data 🔗 Problem-Solving & Combining Data Lesson 018

Important

✨ AI-generated content. This page was written with the assistance of an AI language model and is provided as a learning aid. Despite careful review, it may still contain mistakes, omissions, or out-of-date information. Whether you are new to the topic, a team lead, or a senior practitioner, treat it as a starting point rather than an authoritative reference: read it critically and independently verify anything you act on (code, commands, figures, and factual claims) against official documentation and primary sources before relying on it.

A query inside a query#

Sometimes answering a question requires the result of one query to be used inside another. A subquery is exactly that — a query nested within another query, whose result the outer query uses. Subqueries let you build analyses in layers, answering a preliminary question and then querying against its answer, and they are a step up in SQL expressiveness.

How subqueries work#

A subquery is a SELECT written inside another query, often in the WHERE clause:

SELECT name, amount
FROM   orders
WHERE  amount > (SELECT AVG(amount) FROM orders);

The inner query (SELECT AVG(amount) FROM orders) computes the average amount; the outer query then returns orders above that average. The subquery runs first, produces a value, and the outer query uses it — answering “which orders are above-average?” in one statement, without manually finding the average first.

Where subqueries appear#

Subqueries serve in several positions:

In WHERE, for comparison — comparing each row against a computed value (above average, above a threshold derived from the data), as above.
In WHERE with IN, for membership — testing whether a value is in a set the subquery produces:
```
SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE amount > 1000);
```
This finds customers who placed a large order — the subquery lists the qualifying customer IDs, the outer query returns their names.
In FROM, as a derived table — using a subquery’s result as a table to query further, building a multi-step analysis in layers.
In SELECT, for a computed column — producing a value per row from a related query.

Why subqueries matter#

Subqueries let you express analyses that need an intermediate result — a comparison against an aggregate, a filter based on another table’s contents, a calculation built in stages — without breaking the work into separate manual steps. They keep a multi-step analysis in one repeatable query, and they are the foundation for the layered aggregation the next lesson builds. Much of SQL’s analytical power comes from composing queries this way.

The caveat#

Subqueries can grow hard to read and, sometimes, slow: a deeply nested query is difficult to follow and debug (build and test it in layers, inner query first), and a correlated subquery — one that references the outer query and thus re-runs for every outer row — can be very slow on large data. Often a JOIN accomplishes the same result more efficiently and readably, so when a subquery grows complex, ask whether a JOIN would serve better. And the usual precision caution applies: subqueries involving nulls or empty results can behave unexpectedly (a NOT IN subquery that encounters a null can return no rows), so verify results against expectation. The next lesson combines subqueries with aggregation and conditional logic for real analytical queries.

Hint