What Is a Correlated Subquery?

A subquery is correlated when it refers to a column belonging to the enclosing query. That reference creates a dependency: the subquery cannot be evaluated on its own, so the engine has to evaluate it once for each row of the outer query.

The contrast is with an uncorrelated subquery. WHERE id IN (SELECT id FROM categories WHERE name = 'peripherals') has no reference to the outer table, so the engine can run it once, get a list of four ids, and test each product against that list. A correlated subquery has no such shortcut — the inner query is genuinely different for every outer row.

The cost therefore scales with the outer row count multiplied by the inner work. Ten thousand customers each triggering a scan of the orders table is ten thousand scans, and no amount of clever planning removes that if the subquery genuinely depends on the outer row.

There are two ways to make a correlated subquery cheaper. The first is decorrelation: rewrite it as a join with a GROUP BY, which lets the engine compute the aggregate once per group and then join. SELECT c.name, SUM(o.amount) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id does the same job as the correlated version, and most optimizers will transform one into the other automatically when they can. The second is to make the inner lookup cheap, with an index on the correlated column, so each of the ten thousand executions is a seek rather than a scan.

One related trap: a correlated subquery in the WHERE clause that returns many rows behaves like a nested loop and can be dramatically slower than the equivalent EXISTS. A correlated subquery in the SELECT list is usually fine, because it returns one value per outer row and each execution is a single indexed lookup.

The inner query references the outer row's customer id, which makes it correlated. Each order is evaluated against its own customer's average rather than the global average.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25),
  (4, 2, 900.00);

SELECT o.id,
       c.name,
       o.amount,
       (SELECT ROUND(AVG(o2.amount), 2)
        FROM orders AS o2
        WHERE o2.customer_id = o.customer_id) AS customer_avg
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.id;

Four rows come back. Order 1 is 120.00 against Ada's average of 230.25; order 2 is 340.50 against the same 230.25; order 3 is 75.25 against Grace's average of 487.63; order 4 is 900.00 against the same 487.63. Each order is compared against its own customer's average, not against one global figure.

The correlation is the o2.customer_id = o.customer_id predicate. For each of the four outer rows the engine runs the inner query with a different customer id, so the inner query executes four times rather than once.

That is acceptable here, but the shape to recognise is the one that does not scale. If the orders table had a million rows, this becomes a million executions of the inner aggregate, each an index scan over that customer's orders. The window-function version does the whole thing in one pass: AVG(amount) OVER (PARTITION BY customer_id) computes the per-customer average alongside every row, and you can then compare the row's amount to that value in the same SELECT list.

Two more details worth raising. The correlated subquery returns NULL for a customer with no orders — but here the outer query only contains orders, so that case never arises; in a version that started from customers, you would get NULLs and would need to decide how to display them. And the join to customers is deliberate: it is not needed for the average calculation, but it is what puts the customer name in the output, and it is an ordinary join rather than part of the correlation.