Guide

Correlated subquery in SQL: how it runs and when to rewrite it

Definition, row-by-row execution, examples, and the EXISTS, IN, JOIN and window function rewrites.

By Updated 7 min read

Short answer

A correlated subquery references a column from the outer query, so it cannot run alone. Logically it re-runs once per outer row. Optimizers often rewrite it as a join, so check your database's plan.

On this page
  1. What is a correlated subquery?
  2. How does a correlated subquery execute?
  3. What do correlated subqueries look like in practice?
  4. Should you use EXISTS, IN or a JOIN?
  5. When should you replace it with a window function?
  6. What do you check when a correlated subquery is slow?
  7. Where next?

A correlated subquery is a subquery that uses a column from the outer query, so it cannot run on its own. It is a staple of SQL tutorials and interviews, and it is also the pattern people rewrite when a report gets slow. This guide shows how it runs, three worked examples, and the rewrites. It belongs to our learn SQL section.

The short version

  • Definition: the inner query references an outer-query column, so it depends on the current outer row.
  • Logical model: Microsoft Learn says a correlated subquery is executed once for each row the outer query might select.
  • Not always how it runs: the same page says substituting values into the outer query “isn’t necessarily how SQL Server actually processes” subqueries, and PostgreSQL’s planner converts IN and EXISTS clauses into semijoins or antijoins.
  • EXISTS vs IN: PostgreSQL documents that NOT IN returns null, not true, when the subquery contains a null. NOT EXISTS avoids that.
  • Window functions often replace per-row subqueries for “latest row” and running-total problems.
  • No timings here. We give no speed numbers; use EXPLAIN on your own data.

What is a correlated subquery?

A subquery sits inside another query. It is correlated when its WHERE clause (or select list) mentions a column of the outer query. Compare two subqueries on the sample data below.

-- Uncorrelated: runs on its own, one value
SELECT order_id FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

-- Correlated: needs o.customer_id from the outer row
SELECT o.order_id FROM orders o
WHERE o.amount > (SELECT AVG(o2.amount)
                  FROM orders o2
                  WHERE o2.customer_id = o.customer_id);

The first compares every order to the overall average. The second compares each order to that customer’s own average. Same shape, different question.

Toy data for every example below, defined for this article:

order_idcustomer_idorder_dateamount
1102026-03-0120.00
2102026-03-0960.00
3112026-03-0230.00
4112026-03-1530.00
5122026-03-2090.00

Toy data defined for this article. Columns: orders(order_id, customer_id, order_date, amount).

How does a correlated subquery execute?

Think of it as a loop. For each row of the outer query, plug that row’s value into the inner query, run it, and use the result to decide whether to keep the row. Microsoft’s documentation walks through exactly this: SQL Server considers each row of the outer table, substitutes its value into the inner query, and evaluates the condition.

For the correlated example above:

Outer row (order_id, customer)Inner query runs asInner resultKeep?
1 (10)AVG(amount) WHERE customer_id = 1040.00No, 20 < 40
2 (10)same40.00Yes, 60 > 40
3 (11)... WHERE customer_id = 1130.00No, 30 is not > 30
4 (11)same30.00No
5 (12)... WHERE customer_id = 1290.00No

Only order 2 comes back. Five outer rows meant five inner evaluations in this model.

This is the logical model. It tells you what the answer must be, not what the engine does. Microsoft’s page states that conceptual substitution “isn’t necessarily how SQL Server actually processes Transact-SQL statements with subqueries”. PostgreSQL’s planner README lists “IN or EXISTS WHERE clauses that were converted to semijoins or antijoins” among the things it builds, and says the planner pulls simple subqueries up into the main query’s join tree. So a correlated EXISTS may run as a join. A correlated scalar subquery in a comparison like the one above is harder to rewrite, and may keep its per-row shape. Use EXPLAIN to see which one you got.

For the wider picture of parsing, planning and executing, read the life of a SQL query.

What do correlated subqueries look like in practice?

Scalar subquery in the select list

Return one value per outer row. Microsoft’s example takes the maximum unit price for each order the same way.

SELECT o.order_id, o.customer_id,
       (SELECT MAX(o2.amount)
        FROM orders o2
        WHERE o2.customer_id = o.customer_id) AS customer_max
FROM orders o;

Comparison in WHERE

Keep rows that beat their own group’s figure, as shown earlier. Each customer’s own average is the benchmark.

EXISTS and NOT EXISTS

An existence test. PostgreSQL’s documentation says the subquery “can refer to variables from the surrounding query, which will act as constants during any one evaluation of the subquery”, and that the subquery is run only long enough to determine whether a row is returned.

-- Customers who placed at least one order over 50
SELECT DISTINCT o.customer_id
FROM orders o
WHERE EXISTS (SELECT 1
              FROM orders o2
              WHERE o2.customer_id = o.customer_id
                AND o2.amount > 50);

Should you use EXISTS, IN or a JOIN?

All three can answer “which customers have an order over 50?” They differ on NULLs, duplicates and what you can select.

-- EXISTS (correlated)
SELECT c.customer_id FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.customer_id AND o.amount > 50);

-- IN (uncorrelated)
SELECT c.customer_id FROM customers c
WHERE c.customer_id IN (SELECT customer_id FROM orders WHERE amount > 50);

-- JOIN (needs DISTINCT, or a customer repeats per matching order)
SELECT DISTINCT c.customer_id
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 50;
EXISTSINJOIN
DuplicatesNone; it is a yes or no testNoneRepeats the left row per match unless you add DISTINCT
NULL in the listSafeNOT IN returns null if the list has a nullNot applicable
Columns from the other tableNoNoYes
Reads best for“Is there at least one…”A short, known listWhen you need data from both tables

Behaviors per PostgreSQL’s subquery-expressions page and Microsoft Learn’s subqueries page, checked Nov 17, 2026. Microsoft adds that a join can always be expressed as a subquery, but a subquery can often, not always, be expressed as a join.

The NULL row matters most. PostgreSQL documents that if the subquery yields a null and no equal value, NOT IN returns null, not true. NOT EXISTS does not have that trap.

-- If orders.customer_id contains a NULL, this returns no rows
SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

-- This returns the customers with no orders
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

On performance, the documentation is cautious. Microsoft says that in Transact-SQL there is usually no performance difference between a subquery and an equivalent join, though in some cases where existence must be checked, a join yields better performance. So the rule is: write the clearest version, then read the plan.

When should you replace it with a window function?

Many correlated subqueries answer “compare each row to something about its group”. A window function computes that group figure beside each row in one pass, with no per-row lookup. PostgreSQL’s tutorial describes window functions as calculations across related rows that keep each row’s identity.

-- Correlated: each order's amount vs its customer's average
SELECT o.order_id
FROM orders o
WHERE o.amount > (SELECT AVG(o2.amount)
                  FROM orders o2
                  WHERE o2.customer_id = o.customer_id);

-- Window rewrite (same result on the toy data: order 2)
SELECT order_id
FROM (
  SELECT order_id, amount,
         AVG(amount) OVER (PARTITION BY customer_id) AS cust_avg
  FROM orders
) t
WHERE amount > cust_avg;

The subquery wrapper is required because window functions are allowed only in the select list and ORDER BY, so you cannot put one in WHERE.

The same trick fixes “latest row per group”. This is the old correlated form and its window form.

-- Correlated: latest order per customer
SELECT o.*
FROM orders o
WHERE o.order_date = (SELECT MAX(o2.order_date)
                      FROM orders o2
                      WHERE o2.customer_id = o.customer_id);

-- Window rewrite
SELECT order_id, customer_id, order_date, amount
FROM (
  SELECT *, ROW_NUMBER() OVER (
           PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

The two differ on ties: the correlated version returns every order tied for the latest date, while ROW_NUMBER keeps one. Use RANK if you want ties. Our window functions cheat sheet lists the ranking functions side by side.

What do you check when a correlated subquery is slow?

No timings here, because they depend on your data and engine. Check these in order:

  1. Read the plan. Run EXPLAIN (PostgreSQL) or your engine’s equivalent. If you see a semijoin or a hash join, the engine already rewrote it. If the inner table is scanned once per outer row, that is the cost.
  2. Index the correlation column. The inner query filters on a column such as customer_id. An index there lets each probe avoid scanning the table.
  3. Shrink the outer query first. Filter in the outer WHERE so fewer rows trigger the inner query.
  4. Try the rewrite. Swap IN for EXISTS, or the subquery for a join or window function, and compare plans.
  5. Watch CTE behavior. PostgreSQL inlines a once-referenced, side-effect-free CTE by default, and computes a multiply-referenced one separately unless you write NOT MATERIALIZED. A CTE is not an automatic fix.

Where next?

If you are practicing for interviews, question 21 in our SQL interview questions for data analysts asks this exact topic. Run your experiments in a client from SQL editors compared. For a clause-by-clause refresher, read the SQL window functions cheat sheet.

Questions people ask

What is a correlated subquery?

A correlated subquery is a subquery that uses a column from the outer query, so it cannot be evaluated on its own. Microsoft Learn calls it a repeating subquery and says it is executed once for each row the outer query might select. That is the logical model, and engines may rewrite it.

What is the difference between a correlated and a nested subquery?

A plain (uncorrelated) subquery runs independently, once, and its result is substituted into the outer query. A correlated subquery depends on the outer row, so its result changes row by row. Microsoft Learn describes the uncorrelated case as evaluable as if it were an independent query.

What are the types of subqueries in SQL?

Microsoft Learn lists three basic types: subqueries on lists introduced with IN or modified by ANY or ALL, subqueries introduced with an unmodified comparison operator that must return a single value, and existence tests introduced with EXISTS. Any of them can be correlated.

Which is faster, a CTE or a subquery?

Often neither. PostgreSQL folds a non-recursive, side-effect-free CTE referenced once into the parent query, so it plans like a subquery. If the CTE is referenced more than once, it is computed separately by default. Check the plan with EXPLAIN rather than guessing.

Is a correlated subquery always slow?

No. Microsoft Learn says there is usually no performance difference between a subquery and a semantically equivalent join in Transact-SQL, and PostgreSQL can turn IN and EXISTS clauses into semijoins. It can be slow when the engine cannot rewrite it and the inner table has no useful index. Read the plan.

Sources

  1. Microsoft Learn: Subqueries (SQL Server) ·
  2. PostgreSQL: Subquery expressions (EXISTS, IN, NOT IN) ·
  3. PostgreSQL: WITH queries (CTE inlining and MATERIALIZED) ·
  4. PostgreSQL: optimizer README (semijoins, subquery pull-up) ·
  5. PostgreSQL: window functions tutorial ·
  6. PostgreSQL: EXPLAIN ·

Start hereExplainer

Open