What Correlated Means
A normal subquery runs once and its result is reused. A correlated subquery references a column from the outer query, so it logically runs once per outer row. The inner query depends on the current outer row.
A Typical Use
Correlated subqueries answer per row questions, such as finding employees who earn more than the average in their own department:
- The inner query computes the department average.
- It uses the outer row department to scope that average.
- The outer row passes the filter only if it beats its own group.
EXISTS Is Correlated
The common EXISTS pattern is correlated because the inner query references the outer row key. EXISTS stops at the first match, which can make it efficient.
The Performance Catch
Because the inner query reruns per row, a naive correlated subquery on a large table can be very slow. Optimizers sometimes flatten it into a join, but not always. A window function or a join is often faster.
Key idea
A correlated subquery references the outer row and reruns per row, enabling per group comparisons but risking slow performance on large tables.