A subquery is a query nested inside another query — a small question whose answer feeds a larger one. Master the four places a subquery can live and you unlock some of SQL's most expressive patterns.
What Is a Subquery?
Imagine you walk into a library and ask: "Which books were written by the author who won last year's prize?" The librarian can't answer in one step. First they look up who won, then they fetch that author's books. That nested lookup is exactly what a subquery does in SQL.
A subquery is a SELECT statement embedded inside another SQL statement. The inner query runs first; the outer query uses its result. Subqueries are always wrapped in parentheses and can appear almost anywhere an expression or table is allowed.
Depending on where it sits and how it's written, the result might be a single value, a list of values, an entire virtual table, or a yes/no existence test.
The Sample Dataset
Every example in this article uses the same two tables. You can trace each query by hand using these rows.
customers
| customer_id | name | city |
|---|---|---|
| 1 | Alice | London |
| 2 | Bob | Paris |
| 3 | Carla | Berlin |
| 4 | Diego | Madrid |
orders
| order_id | customer_id | total | status |
|---|---|---|---|
| 101 | 1 | 89.50 | shipped |
| 102 | 1 | 42.00 | pending |
| 103 | 2 | 150.00 | shipped |
| 104 | 3 | 27.75 | cancelled |
| 105 | 5 | 60.00 | shipped |
Two things worth noticing: Diego (customer 4) has no orders at all. Order 105 points at customer_id = 5, who doesn't exist in customers — an orphan row. Both quirks make the examples below more revealing.
Subqueries in WHERE — the Most Common Use
The most frequent home for a subquery is the WHERE clause, where it filters rows based on the result of another query. There are four classic variants.
Single value with =
When the inner query returns exactly one value, you can compare it with =. Here we find all orders placed by Bob without knowing his id in advance:
① Subquery result
| customer_id |
|---|
| 2 |
② Final result
| order_id | total | status |
|---|---|---|
| 103 | 150.00 | shipped |
The inner query returns 2 (Bob's id). The outer query behaves as if you had written WHERE customer_id = 2. A subquery with = must return a single value — if it returns more than one, the database raises an error (a pitfall covered below).
Multiple values with IN
When the inner query returns a list, use IN. This finds every order placed by a customer in London or Paris:
① Subquery result
| customer_id |
|---|
| 1 |
| 2 |
② Final result
| order_id | total | status |
|---|---|---|
| 101 | 89.50 | shipped |
| 102 | 42.00 | pending |
| 103 | 150.00 | shipped |
Existence test with EXISTS / NOT EXISTS
Sometimes you don't care what the inner query returns — only whether it returns anything at all. That is the job of EXISTS. Here we list customers who have placed at least one order:
The database walks through every row in customers and for each one asks: "does at least one matching order exist?" — yes or no. The SELECT 1 is a convention that signals to the reader: "I don't care what is returned, only whether anything is returned." You could write SELECT * or SELECT 'banana' and the behaviour would be identical.
① Check per customer
| name | orders found? |
|---|---|
| Alice | ✓ TRUE |
| Bob | ✓ TRUE |
| Carla | ✓ TRUE |
| Diego | ✗ FALSE |
② Final result
| customer_id | name | city |
|---|---|---|
| 1 | Alice | London |
| 2 | Bob | Paris |
| 3 | Carla | Berlin |
Swap in NOT EXISTS and you get the opposite — Diego, the only customer with no orders.
Comparison with ANY and ALL
ANY and ALL sit between a comparison operator and a subquery returning a column of values. > ANY means "greater than the smallest value in the list"; > ALL means "greater than every value" — i.e. greater than the maximum. IN is exactly equivalent to = ANY.
① Shipped totals
| total |
|---|
| 89.50 |
| 150.00 |
| 60.00 |
② > ANY result (min = 60.00)
| order_id | total |
|---|---|
| 101 | 89.50 |
| 103 | 150.00 |
The minimum shipped total is 60.00, so > ANY returns orders 101 and 103. The maximum is 150.00, so > ALL returns no rows — nothing exceeds 150.00.
Subqueries in FROM — Derived Tables
A subquery in the FROM clause produces a temporary result set that the outer query treats as an ordinary table. These are called derived tables. One rule is non-negotiable: a derived table must be given an alias.
Here we find customers who have placed more than one order. We compute per-customer counts in a subquery, then filter on that count in the outer query:
① Derived table
| customer_id | order_count |
|---|---|
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
| 5 | 1 |
② Final result (count > 1)
| customer_id | order_count |
|---|---|
| 1 | 2 |
Derived tables are the natural solution when you need to filter on the result of an aggregate — something you can't do in a plain WHERE clause, which runs before GROUP BY.
Subqueries in SELECT — Scalar Subqueries
A subquery in the SELECT list must return a single column and a single row — a scalar subquery. Its value becomes a computed column on every output row. If the inner query returns no rows, the value is NULL.
| order_id | total | customer_name |
|---|---|---|
| 101 | 89.50 | Alice |
| 102 | 42.00 | Alice |
| 103 | 150.00 | Bob |
| 104 | 27.75 | Carla |
| 105 | 60.00 | NULL |
Order 105 produces NULL because customer_id = 5 matches no customer — the "no rows means NULL" rule in action. This subquery also references o.customer_id from the outer query, which makes it correlated.
Correlated Subqueries
A correlated subquery references columns from the outer query, so it can't be run on its own. Because the value it depends on changes row by row, the inner query is logically re-evaluated once for every row the outer query processes.
Here we find every order whose total beats the average for orders from the same customer's city:
① Evaluated per row
| name | total | city avg | qualifies? |
|---|---|---|---|
| Alice | 89.50 | 65.75 | ✓ yes |
| Alice | 42.00 | 65.75 | ✗ no |
| Bob | 150.00 | 150.00 | ✗ no |
| Carla | 27.75 | 27.75 | ✗ no |
② Final result
| name | city | total |
|---|---|---|
| Alice | London | 89.50 |
For Alice's 89.50 order, the inner query averages London's totals (89.50 + 42.00 = 65.75); since 89.50 > 65.75, the row qualifies. Bob's 150.00 is exactly equal to Paris's average — not strictly greater, so excluded. The reference c2.city = c.city is the correlation — remove it and the meaning collapses entirely.
Subquery vs JOIN — When to Use Each
Many subqueries can be rewritten as joins and vice versa. Modern query optimizers routinely transform one into the other behind the scenes. Write whichever version is clearest, then check the execution plan with EXPLAIN if performance becomes a concern.
| Situation | Prefer | Why |
|---|---|---|
| Need columns from both tables | JOIN | Subquery can only return one side |
| Large datasets, performance critical | JOIN | Optimizer handles joins more predictably |
| Simple existence check | EXISTS subquery | Reads more naturally; stops at first match |
| Filter on an aggregate | Derived table | Can't filter on GROUP BY result in WHERE directly |
Common Pitfalls
1 — Scalar subquery returns more than one row
Using = with a subquery that returns multiple rows causes an error. If London had two customers, WHERE customer_id = (SELECT ... WHERE city = 'London') would break. Fix: switch to IN or = ANY.
2 — NOT IN with a NULL in the subquery
This is the silent trap. If the inner query of a NOT IN returns even one NULL, the entire condition returns no rows at all — silently. Because NULL means "unknown," SQL can't prove your value is not in a list that contains an unknown element.
3 — Correlated subqueries at scale
A correlated subquery re-executes for every outer row. On a million-row table, that's a million inner executions — the classic "N+1" problem. When performance matters, pre-aggregate in a derived table or rewrite as a join.
4 — Forgetting to alias a derived table
Omit the alias on a FROM-clause subquery and MySQL raises error 1248: "Every derived table must have its own alias." Always append a name — ) AS summary — after the closing parenthesis.
Putting It All Together
A single query combining three techniques: a derived table in FROM, a scalar subquery in SELECT, and a correlated EXISTS in WHERE. It lists every customer who has placed at least one order, sorted from most active to least.
| name | city | order_count |
|---|---|---|
| Alice | London | 2 |
| Bob | Paris | 1 |
| Carla | Berlin | 1 |
The EXISTS filter removes Diego. The scalar subquery counts each remaining customer's orders. The derived table wraps it all so the outer query can sort on that computed column. One statement, three techniques.
Think of a subquery as a question-within-a-question. Decide first what shape of answer you need — a single value (=), a list (IN), a yes/no (EXISTS), or a whole table (derived table) — and the right form follows naturally. Always watch the NOT IN / NULL trap.
Main References
- PostgreSQL Global Development Group — Subquery Expressions — postgresql.org/docs/current/functions-subquery.html
- Oracle Corporation — MySQL 8.0 Reference Manual: Subqueries — dev.mysql.com/doc/refman/8.0/en/subqueries.html
- Microsoft — Subqueries (SQL Server) — learn.microsoft.com/en-us/sql/…/subqueries
- Amazon Web Services — Redshift Developer Guide: Subquery examples — docs.aws.amazon.com/redshift/…/subquery_examples
