Correlated subqueries

Concepts covered: Correlated Subquery

A correlated subquery is a subquery that references a column of the outer query anywhere in its body. The reference usually sits in its WHERE, though one in its select list, HAVING or ON correlates it just as much. The subqueries from the intermediate tier run once: the database computes the list or the scalar, then the outer query uses it. A correlated one cannot run on its own, because a reference such as a.plan has no value until the outer query is sitting on a particular row. So the database evaluates it once per outer row, with that row's values filled in. The outer row keeps its own detail while picking up a value computed from its group.

Each account against its plan's average

The running example for this lesson is a SaaS billing team's accounts table: 8 accounts on 3 plans, each with a seat count. The question is which accounts are larger than the average account on their own plan. GROUP BY plan returns 3 rows, one per plan, and loses the accounts. The correlated subquery keeps all 8 rows and computes the plan average for each one.

SELECT
  a.account_id,
  a.name,
  a.plan,
  a.seats,
  (SELECT AVG(p.seats)
   FROM accounts AS p
   WHERE p.plan = a.plan) AS plan_avg_seats
FROM accounts AS a
ORDER BY a.plan, a.seats;

Read the result by plan. The 3 Team accounts average 25 seats, and Globex at 40 sits above it. The 3 Enterprise accounts average 200, and only Hooli at 300 clears that. Both Free accounts have 1 seat, so each equals its average and neither is above it. The subquery ran 8 times, once per account, and each run saw a different a.plan.

Moving the comparison into WHERE

SELECT a.account_id, a.name, a.seats
FROM accounts AS a
WHERE a.seats > (
  SELECT AVG(p.seats)
  FROM accounts AS p
  WHERE p.plan = a.plan
);

Trace account 3. The outer scan reaches Globex, so a.plan is team. The inner query scans accounts as p, keeps the 3 Team rows, averages 10, 25 and 40 to 25, and hands 25 back. The comparison 40 > 25 holds and Globex is kept. On Wonka the inner query returns 1, the comparison 1 > 1 fails, and the row is dropped. Nothing in the result is a group: every row is still an account, with every column of that account available to the select list.

What the per-row rerun costs

Executed literally, the pattern is a nested loop. With 8 accounts the inner query scans 8 rows on each of 8 passes, so the database touches 64 rows to answer a question about 8. The work grows with the square of the table: at a million accounts the literal plan visits the row count squared. SQL Server and Trino recognise this shape and decorrelate it. They rewrite the subquery as one GROUP BY plan joined back on plan, which computes each average once. PostgreSQL is narrower. It turns correlated EXISTS and NOT EXISTS into semi-joins and anti-joins, and keeps a correlated scalar aggregate like this one as a loop that runs once per outer row.

• 10^12

In PostgreSQL the plan-average query above shows exactly that loop: EXPLAIN prints a SubPlan under the scan of accounts, evaluated for each of the 8 rows. The remedy there is the hand-written form on the right of the comparison below. A CTE groups by plan and is joined back on plan, and it plans as a HashAggregate and a Hash Join. Engines that do decorrelate also have limits. A correlation on an inequality, a LIMIT inside the subquery, or a correlated subquery nested 2 levels deep can leave the optimizer with no equivalent join, and then the loop really runs. A correlated subquery that is slow on a large table is worth rewriting as a CTE, then comparing the 2 plans.

Correlated subquery

• Reads as the question: for each account, average its plan
• Keeps every outer row and every column of it
• Decorrelated into a join by SQL Server and Trino
• Runs as a per-row SubPlan in PostgreSQL, and wherever a rewrite fails

CTE with GROUP BY, then JOIN

• Computes each plan's average once, in a named step
• Plan is predictable: one aggregate, one hash join
• Needs the join key and the aggregate written out
• Reuses the per-plan figures across several comparisons

The bug that removes the correlation

The most common mistake with this pattern leaves the query running and returning the wrong number on every row. Drop the aliases and write WHERE plan = plan inside the subquery, and the query still parses, still runs, and returns one number repeated 8 times.

The subquery is rewritten without aliases as (SELECT AVG(seats) FROM accounts WHERE plan = plan). The query runs and returns 84.625 on every one of the 8 rows. Why?

• Both sides of plan = plan resolve to the inner accounts table, so the condition holds for every row, the subquery is no longer correlated, and it averages all 8 accounts.
• AVG cannot be filtered inside a subquery, so the database ignores the WHERE clause and averages the whole table.
• The database evaluates the subquery once for the first row and caches 84.625 for the rest.
• A NULL plan on one account breaks the comparison for all rows.

The same per-row mechanism powers EXISTS, where the subquery returns no value and the outer row is kept or dropped on whether any inner row matched. WHERE NOT EXISTS (SELECT 1 FROM invoices AS i WHERE i.account_id = a.account_id) keeps the accounts that have never been invoiced, and the database stops scanning invoices at the first match for each account. EXISTS has a lesson of its own; what matters here is that the correlation is the same reference to a, and the same aliasing rule protects it.

About this lesson

This section is part of the Query Structure: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.