Correlated Subqueries Explained

1. What makes a subquery correlated

A correlated subquery is an inner query that, within its own WHERE, SELECT or HAVING clause, references a column of the surrounding, outer query. This reference is the only, but decisive, distinguishing feature compared to a non-correlated subquery: a non-correlated subquery can be executed once, independently of the outer query, and returns a fixed result, a correlated subquery cannot, because its result depends on the respective current row of the outer query.

You recognize this dependency syntactically by a table alias of the outer query showing up inside the inner WHERE condition, for example o.customer_id = c.id, where c comes from the outer FROM clause. If this reference is missing, it is an ordinary, non-correlated subquery, which the database can execute exactly once and reuse the result for all outer rows.

The term "correlated" therefore does not describe a special SQL syntax, but a logical dependency between the inner and outer query. It is exactly this dependency that makes a correlated subquery so powerful for row-by-row logic and, at the same time, the most common cause of unexpectedly slow queries in practice.

2. The mental model: re-evaluated per row

To correctly understand a correlated subquery, the following mental model helps: imagine the database processing the outer query row by row and, for each of these rows, executing the entire inner query separately, plugging in the values of the current outer row as fixed constants into the inner query. With a thousand rows in the outer query, this model means a thousand logically distinct executions of the inner query.

The qualifier "logically" is important: modern database optimizers usually do not actually physically execute these thousand runs a thousand times in practice, but recognize the pattern and internally rewrite it into a more efficient join or semi-join, a process called decorrelation. The mental model "one execution per outer row" therefore describes the semantics, not necessarily the actual physical execution, and exactly this gap between semantics and execution is the source of many misunderstandings around correlated subqueries.

-- Sample tables
-- customers: id, name
-- orders: id, customer_id, amount, order_date

-- Correlated subquery: highest single order amount per customer
SELECT
  c.name,
  (
    SELECT MAX(o.amount)
    FROM orders o
    WHERE o.customer_id = c.id   -- references the outer row c.id
  ) AS highest_order
FROM customers c;

-- Conceptually, for each row of customers, the database evaluates:
-- SELECT MAX(amount) FROM orders WHERE customer_id = <this row's id>

3. Correlated subqueries in WHERE, SELECT and HAVING

A correlated subquery can appear in three places of a query, each with a different business meaning. In the WHERE clause it filters outer rows, usually via EXISTS, NOT EXISTS or a scalar comparison. In the SELECT list it computes an additional value per row, as in the highest order amount example from the previous section. In the HAVING clause it filters after grouping based on a value that in turn depends on the current group.

The most common place to encounter correlated subqueries is in combination with EXISTS, because this construct only checks whether at least one matching inner row exists, without returning the specific values of that row. This restriction to a pure yes/no answer allows the optimizer to abort execution early as soon as the first matching row is found, instead of exhaustively searching through all matching rows.

-- Correlated subquery in WHERE with EXISTS
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.amount > 200
);

-- Correlated subquery in HAVING: customers whose average order
-- exceeds their own highest single order minus a fixed discount
SELECT c.id, AVG(o.amount) AS avg_amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
HAVING AVG(o.amount) > (
  SELECT MAX(o2.amount) - 50
  FROM orders o2
  WHERE o2.customer_id = c.id
);

4. The nested loop execution plan in detail

The execution plan a database chooses for a non-decorrelated correlated subquery is typically a nested loop join: for every row of the outer table, the inner query gets executed once, usually as an index lookup if a suitable index exists, or as a full table scan if none exists. With N outer rows and an index lookup with logarithmic complexity, the total complexity works out to roughly N * log(M), where M is the size of the inner table, which is usually acceptable for moderate table sizes.

If the index on the inner table's join column is missing, the same nested loop plan turns into a disaster: each of the N outer rows triggers a full scan of the inner table with M rows, resulting in a total complexity of N * M. With two tables of ten thousand rows each, that is a hundred million comparison operations, instead of the few tens of thousands an index-based access would need. This difference explains why one and the same correlated subquery can be lightning fast on a small test database and unbearably slow on the production database with real data volumes.

-- Without an index on orders.customer_id, this triggers
-- a full scan of orders for every single row of customers
EXPLAIN ANALYZE
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- The index turns each inner lookup from O(M) into O(log M)
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Re-run EXPLAIN ANALYZE afterward and compare the "actual time"
-- and "rows removed by filter" values in the plan output

5. The classic performance trap on large tables

The most common performance trap with correlated subqueries does not arise from the subquery itself, but from the interplay of a missing index, growing table size and a complex inner condition that the optimizer cannot decorrelate. A subquery that runs in milliseconds in the development environment with a hundred test rows can suddenly take minutes in production with a million rows, because the quadratic instead of linear complexity from section four only becomes visible at realistic data volumes.

A second, subtler case arises when the inner query itself contains expensive aggregations or additional joins. If such a subquery is re-executed per outer row instead of being aggregated once, the effort for each of these aggregations multiplies by the factor of the outer row count. This pattern often goes unnoticed in code reviews, because the query looks syntactically harmless even though it is content-wise very expensive.

-- Expensive: complex aggregation re-evaluated for every outer row
SELECT c.name
FROM customers c
WHERE (
  SELECT COUNT(*)
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.customer_id = c.id
) > 10;

-- Pre-aggregate once instead of re-computing per outer row
SELECT c.name
FROM customers c
JOIN (
  SELECT o.customer_id, COUNT(*) AS item_count
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.customer_id
) counts ON counts.customer_id = c.id
WHERE counts.item_count > 10;

6. Rewriting into a join

The most reliable method to speed up a slow correlated subquery is manually rewriting it into a join, usually supported by a pre-aggregating derived table. This rewrite forces the inner query to execute exactly once, independent of the row count of the outer query, and afterward leaves the database to perform a regular, usually hash-based join between two already finished result sets. The example from section five already shows this pattern: the aggregation moves into a separate, once-executed derived table.

For the existence check case with EXISTS, the direct rewrite into INNER JOIN with DISTINCT is semantically similar, but not always the better choice, because DISTINCT itself is an expensive sort or hash operation. In most cases the recommendation is therefore more nuanced: keep EXISTS for pure existence checks, because modern optimizers already turn these into a semi-join very well, and only actively rewrite scalar or aggregating correlated subqueries into a join with a derived table.

-- Correlated subquery for the highest order amount, revisited
SELECT c.name, sub.highest_order
FROM customers c
LEFT JOIN (
  SELECT customer_id, MAX(amount) AS highest_order
  FROM orders
  GROUP BY customer_id
) sub ON sub.customer_id = c.id;
-- the inner aggregation now runs exactly once, not once per customer

7. Rewriting into a window function

For many correlated subqueries that compute a value relative to the current row, for example a rank, a running total, or a comparison with the maximum of the row's own group, a window function is the more elegant and usually more performant alternative to a derived table. A window function reads the data once, sorts it once, and computes the result for all rows in a single pass, without needing a physical second copy of the table.

The example from section six with the highest order amount per customer can be expressed directly with MAX() OVER (PARTITION BY ...), which is syntactically more compact and, as a rule, executed more efficiently than the corresponding correlated subquery, because the database handles the partitioning and aggregation in a single scan of the table.

-- Same result as the correlated subquery in section 2,
-- expressed with a window function instead
SELECT DISTINCT
  c.name,
  MAX(o.amount) OVER (PARTITION BY o.customer_id) AS highest_order
FROM customers c
JOIN orders o ON o.customer_id = c.id;

-- Ranking example: rank each order by amount within its customer
SELECT
  o.customer_id,
  o.id AS order_id,
  o.amount,
  RANK() OVER (PARTITION BY o.customer_id ORDER BY o.amount DESC) AS rank_in_customer
FROM orders o;

8. When a correlated subquery stays the right choice

Despite all the performance warnings, the correlated subquery should not be avoided on principle. For pure existence checks with EXISTS or NOT EXISTS, it is usually both the most readable and, thanks to good optimizer support, a performant solution that needs no rewriting at all. For small, infrequently run queries too, for example administrative reports with a manageable row count, the extra effort of a rewrite usually is not worth it, because the absolute time saved remains small.

The decision to rewrite should therefore always be made based on data: a look at the execution plan with EXPLAIN ANALYZE reliably shows whether the database already handles the correlated subquery efficiently, for example as a semi-join or anti-join, or whether an actually expensive nested loop with repeated scans results. Only when the latter is the case and the affected query runs regularly or on large tables does the effort of a manual rewrite become justified.

9. Rewrite strategies compared

The table below summarizes which rewrite strategy typically fits best for which use case of a correlated subquery.

This table does not replace a measurement, but provides an initial estimate. Reliable proof of whether a rewrite is necessary always comes only from comparing the actual execution plans on realistic data volumes.

10. Summary

A correlated subquery differs from an ordinary subquery in exactly one point: it references a column of the outer query and is therefore logically re-evaluated for every outer row. This mental model explains both its strength for row-by-row computations and its risk when the optimizer cannot decorrelate the repeated execution and a nested loop plan with quadratic complexity results instead.

Existence checks with EXISTS usually benefit from good automatic optimization and rarely need to be rewritten. Scalar and aggregating correlated subqueries can reliably be rewritten into a pre-aggregating derived table or a join, while rank and comparison computations are almost always better formulated as a window function. The execution plan with EXPLAIN ANALYZE is in every case the only reliable basis for deciding whether a rewrite is even necessary.

11. FAQ: Correlated Subqueries Explained