Correlated subqueries

A correlated subquery references a column from the outer query. It re-executes for each row of the outer query.

Example: employees earning above their department average

SELECT first_name, last_name, salary, department_id
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees inner_e
  WHERE inner_e.department_id = e.department_id  -- references outer row
);

e.department_id links the inner query to the current outer row. For each employee, the inner query calculates that employee's department average.

How it differs from a regular subquery

Correlated EXISTS

-- Patients who have at least one completed appointment
SELECT first_name, last_name
FROM patients p
WHERE EXISTS (
  SELECT 1 FROM appointments a
  WHERE a.patient_id = p.id
    AND a.status = 'completed'
);

Performance consideration

Correlated subqueries can be slow on large tables — they run N times (once per outer row). For better performance, rewrite as a JOIN with a derived table:

-- Equivalent and usually faster
SELECT e.first_name, e.last_name, e.salary, e.department_id
FROM employees e
JOIN (
  SELECT department_id, AVG(salary) AS dept_avg
  FROM employees
  GROUP BY department_id
) dept ON dept.department_id = e.department_id
WHERE e.salary > dept.dept_avg;

Or use a window function:

SELECT first_name, last_name, salary, department_id
FROM (
  SELECT *,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
  FROM employees
) t
WHERE salary > dept_avg;

Tips

• Use correlated subqueries for clarity when tables are small.
• If EXPLAIN ANALYZE shows "Seq Scan" on a large table inside a subquery loop, rewrite as a JOIN.