A query inside a query. The inner one runs first and hands its answer to the outer one — except in the correlated case, where it runs again for every single row. Watch that happen.
Overview
Quick Context
You cannot write WHERE salary > AVG(salary) — an aggregate over the whole table is not something a row-by-row filter can evaluate. What you can do is compute the average in its own query and use the result: that inner query is a subquery.
Everything else follows from what the subquery returns: one value, one column, or a whole table. And from one further question — whether it depends on the outer row or not.
1Inner query result
2 Outer query — every row tested against that answer
3 Result
Subqueries: A Query That Answers a Question for Another Query
Four shapes, one idea — and one of them costs a great deal more than the others.
The four shapes
Scalar — returns exactly one row and one column, so it can stand anywhere a single value can: in SELECT, in WHERE, in an expression. If it ever returns two rows, the query fails at runtime, which is why a scalar subquery usually contains an aggregate.
IN list — returns one column of any length, used as the right-hand side of IN. The classic membership test. Beware NOT IN against a column containing NULL: the comparison goes UNKNOWN and the whole query returns nothing, silently.
Derived table — a whole result set used in the FROM clause as if it were a table. It needs an alias. This is how you filter on an aggregate you just computed, and it is the same tool a CTE gives you with a readable name attached.
Correlated — references a column from the outer query, so it cannot be computed once up front. Conceptually it runs again for every candidate row, with that row's value substituted in.
Why correlated is the one to understand
Compare the two comparisons in the lab. The scalar version asks "is this person paid more than the company average?" — one number, computed once, tested against all eight rows. The correlated version asks "is this person paid more than their own department's average?" — a different number per row, so the inner query runs eight times.
The answers differ, and the difference is the point: one employee sits below the company average and above their department's. No single precomputed number could have produced that answer.
Note the "conceptually" above. The optimiser is free to rewrite a correlated subquery into a join or a grouped aggregate, and usually does. But it is not obliged to, and when it cannot, you get one inner execution per outer row — the classic accidentally-quadratic query.
EXISTS, and when to prefer it
EXISTS takes a correlated subquery and asks only whether it produced any row at all, so the engine can stop at the first match instead of building the full result. For "does this customer have any order?" it is both clearer and cheaper than IN.
It also sidesteps the NULL trap: NOT EXISTS behaves the way people expect NOT IN to behave.
Subquery, join or CTE?
Most subqueries can be written as a join, and joins are often faster because the planner has more freedom with them. Prefer a subquery when it says what you mean more plainly — a membership test reads better as IN or EXISTS than as a join you then have to de-duplicate.
Once a derived table is more than a couple of lines, or you need it twice, lift it into a CTE. Same execution, a name, and no nesting to read inside-out.
A query inside a query, and where it can sit
A subquery is a SELECT wrapped in parentheses and used as part of a larger statement. What it can be used as depends on what it returns.
A single value (scalar). Anywhere a value is allowed:
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
A list of values. With IN, ANY or ALL:
SELECT name FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE budget > 100000);
A table. In FROM, where it is called a derived table and must be given an alias:
SELECT dept, avg_salary
FROM (SELECT dept_id AS dept, AVG(salary) AS avg_salary
FROM employees GROUP BY dept_id) AS dept_avgs
WHERE avg_salary > 60000;
An existence test. With EXISTS, where the returned columns are irrelevant:
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
That last shape is the one to become comfortable with, because it introduces the distinction that matters most.
Correlated or not: the distinction that decides performance
A non-correlated subquery can run on its own. SELECT AVG(salary) FROM employees needs nothing from the outer query, so the engine computes it once and reuses the answer.
A correlated subquery references a column from the outer query, so conceptually it must run again for every outer row:
SELECT name, salary
FROM employees e
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE dept_id = e.dept_id); -- e.dept_id ties them together
This reads beautifully — "everyone earning above their own department's average" — and the naive execution is one inner query per employee. With 100,000 employees that is 100,000 aggregate queries.
Modern planners often rewrite correlated subqueries into joins automatically, so the disaster is not guaranteed. But "often" is not "always", and the safe habit is: if a correlated subquery appears in a slow query, rewriting it as a join or a window function is the first thing to try.
The window function version of the query above avoids the issue entirely:
SELECT name, salary
FROM (SELECT name, salary,
AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees) t
WHERE salary > dept_avg;
EXISTS, IN and JOIN for the same question
Three ways to ask "which customers have placed an order":
-- 1. IN
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);
-- 2. EXISTS
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- 3. JOIN
SELECT DISTINCT c.name
FROM customers c JOIN orders o ON o.customer_id = c.id;
All three usually produce the same plan on a modern engine. The differences that remain are worth knowing:
EXISTS stops at the first match, which is exactly the semantics of the question. It also handles NULLs correctly.
IN with a subquery that can return NULL behaves as expected; the danger is NOT IN, which returns nothing at all if a single NULL appears in the list. Prefer NOT EXISTS for the negative case, always.
The join needs DISTINCT to avoid duplicating customers with several orders, and DISTINCT costs a sort. If you also want columns from orders, the join is the only option of the three.
Rule of thumb: EXISTS/NOT EXISTS when you only want to test for presence, a join when you need data from both tables, and IN for short literal lists.
The three shapes a subquery can take
A subquery is a query used as a value, a set, or a table, and which of the three it is decides where it can appear and how the database runs it. Correlated subqueries are the ones worth watching, because they run once per outer row.
query.sqlSQLite
-- 1. As a VALUE: one row, one column, used where a scalar goes.
SELECT name, price,
(SELECT round(avg(price), 2) FROM products) AS avg_price,
round(price - (SELECT avg(price) FROM products), 2) AS vs_avg
FROM products
ORDER BY price DESC;
-- 2. As a SET: one column, many rows, used with IN or EXISTS.
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'shipped')
ORDER BY name;
-- 3. As a TABLE: many columns, given a name, joined like any other.
SELECT c.name, t.orders, t.last_order
FROM customers c
JOIN (SELECT customer_id, count(*) AS orders, max(placed) AS last_order
FROM orders GROUP BY customer_id) t
ON t.customer_id = c.id
ORDER BY t.orders DESC, c.name;
-- The one to be careful with is a CORRELATED subquery -- one that refers
-- to the outer query, so it cannot be evaluated once and reused.
SELECT c.name,
(SELECT count(*) FROM orders o WHERE o.customer_id = c.id) AS orders
FROM customers c
ORDER BY orders DESC, c.name;
-- That last query is readable and runs its inner SELECT once per
-- customer. The JOIN version above computes the same answer in one pass.
-- On five rows the difference is nothing; on five million it is the
-- difference between a query that returns and one that does not.
--
-- Note also that the correlated version finds Dara and Eze with 0, and
-- the IN version does not list them at all. A subquery's shape changes
-- which rows survive, not just how fast the answer arrives.
Result
Guided experiments
Start with the scalar case. The inner query produces one number, the company average of 84250, and all eight rows are tested against it. Inner query runs: 1.
Switch to the IN list. Now the inner query returns a column — the departments with a budget over 500,000 — and the outer query keeps any employee whose department is in it. Still one execution.
Switch to the derived table. The inner query returns a three-row table of per-department averages — 82333.33, 86666.67 and 83500 — and the outer query filters that, keeping the two above 83000. This is the shape to reach for when you want to filter on something you had to compute first.
Switch to correlated and press Play. The inner query re-runs for each row with that row's department substituted in, and you can watch the substituted text change. Inner query runs climbs to 8.
Find the row that disagrees. Grace clears her department's average but not the company's, so she is returned by the correlated query and rejected by the scalar one. Same table, same operator, different question.
Step through it by hand. Press Reset then Step. Each press is one execution of the inner query, which is exactly the cost model you are being warned about when someone says "correlated subquery" in a code review.
Worth remembering
A subquery is just a query whose result another query consumes, and its shape is decided by what it returns: one value (scalar), one column (IN), or a table (derived, which must be aliased). An uncorrelated subquery runs once, before the outer query. A correlated one names a column from the outer row, so conceptually it runs once per row — which is both why it can answer per-group questions no single precomputed value could, and why it is the first thing to look at when a query is unexpectedly slow. Reach for EXISTS when you only need to know whether a match exists, and lift anything long or reused out into a CTE.
Subquery, derived table, or CTE?
A CTE (WITH name AS (...)) does the same job as a derived table with a different syntax, and the choice is mostly about readability — with two real differences.
Subquery / derived table
CTE
Readability with several steps
Deteriorates fast when nested
Reads top to bottom
Reusing the same result twice
Must be written twice
Written once, referenced twice
Recursion
Not possible
WITH RECURSIVE
Optimisation
Always inlined
Inlined in modern engines; older PostgreSQL materialised it
The reuse point is the practical one. If the same derived table appears twice in a query, a CTE states it once and makes the intent obvious.
The optimisation note matters if you work on older systems: PostgreSQL before version 12 treated every CTE as an optimisation fence, materialising it whether or not that was efficient. Since 12 it inlines them by default, with MATERIALIZED and NOT MATERIALIZED available to override.
For anything with more than two steps, a chain of CTEs is almost always the more maintainable choice:
WITH recent_orders AS (
SELECT * FROM orders WHERE order_date >= '2026-01-01'
),
customer_totals AS (
SELECT customer_id, SUM(total) AS spend
FROM recent_orders GROUP BY customer_id
)
SELECT c.name, t.spend
FROM customer_totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.spend > 1000;
Common mistakes with subqueries
A scalar subquery returning more than one row. The engine raises an error at runtime, and only when the data grows enough to produce a second row — so this bug ships. Guard with LIMIT 1 and an explicit ORDER BY, or use MIN/MAX.
NOT IN with NULLs. Covered above, and worth repeating because it fails silently rather than loudly.
Forgetting the alias on a derived table. Most engines require one; the error message is clear but the cause is not obvious the first time.
Correlated subqueries in SELECT.SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) FROM customers c runs one count per customer. A LEFT JOIN with GROUP BY, or a window function, does it in one pass.
Referring to an outer alias inside a non-correlated subquery. Scope runs outwards, not inwards: the outer query cannot see the subquery's columns.
Questions people ask
Are subqueries slower than joins? Not inherently. Non-correlated subqueries run once. Correlated ones may run per row unless the planner rewrites them, which is where the reputation comes from.
Can I use a subquery in UPDATE or DELETE? Yes, and it is one of the most useful places for them — DELETE FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE inactive).
How deep can I nest them? Deeper than you should. Past two levels, rewrite as CTEs — the query becomes readable and the plan rarely changes.
Does EXISTS care what the subquery selects? No. SELECT 1, SELECT * and SELECT NULL are equivalent; the engine only checks whether a row is produced.
Can a subquery reference the table it is inside? Yes, and it is a normal pattern — "employees earning more than their department's average" does exactly that.
What is a lateral join? A subquery in FROM that is allowed to reference earlier tables in the same FROM clause (LATERAL in PostgreSQL, CROSS APPLY in SQL Server). It is the clean way to write "the three most recent orders for each customer".
Recap in one screen
A subquery can act as a value, a list, a table or an existence test, depending on what it returns.
Non-correlated subqueries run once; correlated ones reference the outer row and may run per row.
Use EXISTS/NOT EXISTS for presence tests, joins when you need columns from both sides.
NOT IN plus a NULL returns nothing at all — the most dangerous silent failure in SQL.
Beyond two levels of nesting, switch to CTEs for readability.
Predict, then reveal
About to run: Start with the scalar case. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Recall check
0 of 4
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What is meant by “A scalar subquery returning more than one row” here?
The engine raises an error at runtime, and only when the data grows enough to produce a second row — so this bug ships. Guard with LIMIT 1 and an explicit ORDER BY , or use MIN / MAX .
What is meant by “NOT IN with NULLs” here?
Covered above, and worth repeating because it fails silently rather than loudly.
What is meant by “Forgetting the alias on a derived table” here?
Most engines require one; the error message is clear but the cause is not obvious the first time.
What is meant by “Correlated subqueries in SELECT” here?
SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) FROM customers c runs one count per customer. A LEFT JOIN with GROUP BY , or a window function, does it in one pass.
Cheat sheet
Subqueries in SQL
A query inside a query. The inner one runs first and hands its answer to the outer one — except in the correlated case, where it runs again for every single row. Watch that happen.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.