What is a correlated subquery and how does it perform?

The correlation is the reference to an outer column inside the subquery. That reference ties the subquery to each candidate row, which is why correlated subqueries are often described as running once per outer row.

SELECT e.id, e.name, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(x.salary)
  FROM employees x
  WHERE x.department_id = e.department_id
);

How engines handle them

• A naive plan runs the subquery for every outer row, which becomes expensive
• Optimizers can decorrelate into a join or a window function
• An index on the correlation column, here department_id, helps the naive plan
• EXISTS and IN are often decorrelated into semi-joins
• A window function can express the same logic in one pass

The equivalent window-function form partitions by department and computes the average once. It is often easier for the planner to execute efficiently, but it is not always faster, so compare plans on realistic data.

WITH ranked AS (
  SELECT id, name, salary,
         AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
  FROM employees
)
SELECT id, name, salary
FROM ranked
WHERE salary > dept_avg;