What is a correlated subquery, and how does it differ from a non-correlated one?

Non-correlated subquery

Self-contained — it doesn't reference anything from the outer query, so it only needs to be evaluated once:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

The inner SELECT AVG(salary) FROM employees computes one number, once, no matter how many outer rows there are.

Correlated subquery

References a column from the outer query, so its result depends on which outer row is currently being evaluated:

SELECT e.name, e.salary, e.department
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e.department   -- <-- correlated: references outer e.department
);

Here, the inner query's result (average salary) differs for every department, so conceptually it must be recomputed for each outer row.

Common use case: "latest/top row per group"

SELECT o.*
FROM orders o
WHERE o.order_date = (
    SELECT MAX(o2.order_date)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id   -- correlated
);

This finds each customer's most recent order — a pattern that's hard to express without either a correlated subquery, a window function (ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC)), or a GROUP BY plus a join.

Performance implications

A naive mental model — "the subquery runs once per outer row" — describes correctness, not necessarily execution. Modern optimizers frequently rewrite correlated subqueries into semi-joins or anti-joins, especially ones using EXISTS/IN/NOT EXISTS, avoiding a literal row-by-row re-execution. But not every correlated subquery can be rewritten this way. On an engine or query shape the optimizer can't flatten, you get an actual nested-loop-style re-execution per outer row — O(n×m), which can be dramatically slower than an equivalent join or window function on large tables.

Practical guidance: correlated subqueries are fine, and often the clearest way to express "compare each row to something computed from its own group." But when one causes a performance problem on a large table, check the execution plan first. A rewrite to a JOIN with GROUP BY, or a window function, is often available and can be an order of magnitude faster.