What is the difference between a correlated and an uncorrelated subquery, and when does the distinction matter for performance?

How to think about it

The thing the interviewer is really checking here is whether you understand execution semantics — not just “correlated references the outer query,” but what that means at runtime. A correlated subquery that runs once per outer row is a loop hidden inside your SQL, and on a large table it quietly becomes an O(n²) operation.

Uncorrelated: runs exactly once

An uncorrelated subquery is self-contained. The engine evaluates it once, caches the scalar result, and uses that single value for every row in the outer query.

-- Uncorrelated: the inner SELECT runs once and returns one number
SELECT employee_id, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

The database computes the global average once, then scans the employees table comparing each salary against it.

Correlated: runs once per outer row

A correlated subquery references at least one column from the outer query. The engine must re-evaluate it for every row of the outer result set — it cannot cache the result because the answer changes for each row.

-- Correlated: the inner SELECT re-runs once per employee row
SELECT e.employee_id, e.name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id  -- ties back to the outer row
);

This finds employees who earn above their own department’s average. With 500 employees, the inner query runs 500 times.

The rewrite that fixes it

Almost every correlated subquery can be rewritten as a CTE or derived table that pre-aggregates once, then joins:

The key insight: hidden loops

The correlated pattern is not always wrong — sometimes it’s the most readable way to express complex logic and the optimizer will convert it to a join anyway. But when EXPLAIN shows “SubPlan” or “Loops: N” on a large table, that is a signal to rewrite.