Correlated subquery

A correlated subquery is a SQL subquery that refers to columns in the outer query. It executes once for each row in the outer query which means it can significantly slow down your query.

Here’s an example of a correlated subquery that returns all the employees who earn more than the average salary in their department.

SELECT e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

The subquery references the e1.department_id column in the outer query, meaning it must run once per row in the outer query.

Links

• Uncorrelated subquery