SQL Subquery: WHERE, FROM and Correlated Examples
· 7 min read
A SQL subquery is a query inside another query, written in brackets. The inner query runs first (logically), and the outer query uses its result as a value, a list, or a table. The most common use is a subquery in WHERE that compares each row with a number you would otherwise have to calculate by hand.
SELECT order_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders)
ORDER BY order_id;This finds every order above the average order amount, without typing the average in. Below: the output on real rows, subqueries that return a list or a whole table, correlated subqueries, when a join is the better choice, and the NOT IN bug that makes results disappear.
A worked example
| customer_id | customer_name | region |
|---|---|---|
| 1 | Acme Dental | North |
| 2 | Birch Cafe | South |
| 3 | Cobalt Labs | North |
| 4 | Dune Studio | West |
| 5 | Elm Bakery | South |
| order_id | customer_id | order_date | amount |
|---|---|---|---|
| 101 | 1 | 2025-03-03 | 120.00 |
| 102 | 1 | 2025-03-18 | 480.00 |
| 103 | 2 | 2025-03-07 | 90.00 |
| 104 | 3 | 2025-04-02 | 300.00 |
| 105 | 3 | 2025-04-20 | 260.00 |
| 106 | 2 | 2025-04-11 | 150.00 |
| 107 | NULL | 2025-04-15 | 75.00 |
Order 107 is a walk-in sale with no customer attached. Keep an eye on it.
The inner query, SELECT AVG(amount) FROM orders, returns one number: 210.71. The outer query then keeps orders above it:
SELECT order_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders)
ORDER BY order_id;| order_id | amount |
|---|---|
| 102 | 480.00 |
| 104 | 300.00 |
| 105 | 260.00 |
A subquery that returns exactly one value is called a scalar subquery. You can use it anywhere a single value fits, including in SELECT: amount - (SELECT AVG(amount) FROM orders) AS vs_average shows how far each order is from the average.
If the subquery returns more than one row where SQL expects one value, the query fails. DuckDB says More than one row returned by a subquery used as an expression, Postgres says more than one row returned by a subquery used as an expression. When you see that, the subquery is missing a filter or an aggregate.
Subquery in WHERE with IN
A subquery can also return a list. Pair it with IN to ask "is this value one of those?":
SELECT customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE order_date >= '2025-04-01' AND order_date < '2025-05-01'
)
ORDER BY customer_name;| customer_name |
|---|
| Birch Cafe |
| Cobalt Labs |
These are the customers who ordered in April. Cobalt Labs ordered twice that month but appears once, because IN only asks whether a match exists. That detail matters in the next section.
Subquery in FROM
Put a subquery in FROM and it acts like a temporary table. This is how you aggregate in two steps: first per customer, then per region.
SELECT region, AVG(customer_total) AS avg_spend_per_customer
FROM (
SELECT c.region, o.customer_id, SUM(o.amount) AS customer_total
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
GROUP BY c.region, o.customer_id
) AS per_customer
GROUP BY region
ORDER BY region;| region | avg_spend_per_customer |
|---|---|
| North | 580 |
| South | 240 |
North has Acme Dental (600) and Cobalt Labs (560), so the average spend per customer is 580. A single AVG(amount) grouped by region would give the average order instead, which answers a different question. Getting the grain right like this is the whole point of the inner step. SQL Server, MySQL and Postgres before version 16 require the alias (AS per_customer). DuckDB does not, but always add one.
When subqueries in FROM start to nest, rewrite them as a CTE. It is the same logic, read top to bottom: see SQL CTE explained.
Correlated subquery
A correlated subquery refers to the row the outer query is looking at, so it is evaluated once per outer row. Here it finds orders bigger than that customer's own average:
SELECT o.order_id, o.customer_id, o.amount
FROM orders AS o
WHERE o.amount > (
SELECT AVG(o2.amount)
FROM orders AS o2
WHERE o2.customer_id = o.customer_id
)
ORDER BY o.order_id;| order_id | customer_id | amount |
|---|---|---|
| 102 | 1 | 480.00 |
| 104 | 3 | 300.00 |
| 106 | 2 | 150.00 |
The link is o2.customer_id = o.customer_id. For order 102, the inner query averages Acme Dental's orders (300); for order 106, Birch Cafe's (120). Order 107 drops out because its customer is NULL and NULL = NULL is never true.
Correlated subqueries read naturally but can be slow on big tables. A window function, AVG(amount) OVER (PARTITION BY customer_id), gives the same per-customer average in one pass: see SQL window functions.
Subquery vs join
Many subqueries can be written as joins, and the results are not always identical. Here is the April question as a join:
SELECT c.customer_name
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_date >= '2025-04-01' AND o.order_date < '2025-05-01'
ORDER BY c.customer_name;| customer_name |
|---|
| Birch Cafe |
| Cobalt Labs |
| Cobalt Labs |
The join returns one row per matching order, so Cobalt Labs appears twice. SELECT DISTINCT fixes it, but it is a sign you used the wrong tool.
A rule of thumb:
| Use | When |
|---|---|
Subquery with IN or EXISTS | You only need to know whether a match exists |
| Join | You need columns from both tables in the output |
Subquery in FROM or a CTE | You need to aggregate in two steps |
Modern databases often run a subquery and the equivalent join at the same speed, so choose the version that matches the question. How joins themselves work is in how to join two tables.
Common mistakes
NOT IN with a NULL in the list (the AI mistake to watch for)
Ask an AI assistant for "customers who have never ordered" and this is a very common answer:
SELECT customer_name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);| customer_name |
|---|
Empty. Dune Studio and Elm Bakery have never ordered, yet nothing comes back. The cause is order 107: its customer_id is NULL, so the list contains a NULL. x NOT IN (1, 2, 3, NULL) can never be true, because SQL cannot rule out that x equals the unknown value. One empty cell makes the whole result disappear, and the assistant may tell you "every customer has placed an order".
Use NOT EXISTS, which is not affected by NULLs:
SELECT c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_name;| customer_name |
|---|
| Dune Studio |
| Elm Bakery |
Adding WHERE customer_id IS NOT NULL inside the NOT IN subquery also works. A LEFT JOIN ... IS NULL gives the same answer too.
How to spot it: an empty result from NOT IN is almost always this bug. Run SELECT COUNT(*) FROM orders WHERE customer_id IS NULL; if it is more than 0, switch to NOT EXISTS. More on NULL behaviour in SQL COALESCE and NULL.
Forgetting to correlate
If you leave out WHERE o2.customer_id = o.customer_id, the inner query averages all orders, and you are back to the first example. The query still runs, so check one row by hand.
Using = with a subquery that returns a list
WHERE customer_id = (SELECT customer_id FROM orders WHERE ...) works only while the subquery happens to return one row. Use IN whenever it can return several.
FAQ
What is a subquery in SQL?
A SELECT inside another statement, wrapped in brackets. It can return one value (used in WHERE or SELECT), a list (used with IN or EXISTS), or a table (used in FROM).
Is a subquery faster than a join?
Not in general. Most databases rewrite simple IN subqueries into joins internally. Correlated subqueries on large tables can be slower, and a join or window function is often faster there.
What is the difference between a correlated and a non-correlated subquery?
A non-correlated subquery can run on its own and returns the same result for every outer row. A correlated subquery uses a column from the outer query, so its result changes from row to row.
Can I use a subquery in SELECT?
Yes, as long as it returns one value per row. (SELECT AVG(amount) FROM orders) in the SELECT list adds the overall average to every row.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Average order value by segment” →