Correlated Subqueries

-- Reference the outer query's alias inside the subquery.
SELECT column_name
FROM table AS outer_alias
WHERE column_name comparison_operator (
    SELECT aggregate_function(column_name)
    FROM table AS inner_alias
    WHERE inner_alias.group_key = outer_alias.group_key
);

-- Correlated subquery combined with EXISTS.
SELECT column_name
FROM table AS a
WHERE EXISTS (
    SELECT 1
    FROM other_table AS b
    WHERE b.foreign_key = a.primary_key
      AND b.condition_column = 'condition_value'
);

-- Retrieve employees whose salary is higher than the average salary in their own department.
SELECT emp.employee_name, emp.department, emp.salary
FROM employees AS emp
WHERE emp.salary > (
    SELECT AVG(sub.salary)
    FROM employees AS sub
    WHERE sub.department = emp.department
)
ORDER BY emp.department, emp.salary DESC;

-- Use EXISTS to retrieve customers whose most recent order is from 2024 or later.
SELECT c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.order_date >= '2024-01-01'
);

-- Use the SELECT clause to calculate each employee's rank within their department.
SELECT
    emp.employee_name,
    emp.department,
    emp.salary,
    (
        SELECT COUNT(*)
        FROM employees AS sub
        WHERE sub.department = emp.department
          AND sub.salary > emp.salary
    ) + 1 AS department_rank
FROM employees AS emp
ORDER BY emp.department, department_rank;

-- Employees earning above the department average.
employee_name | department  | salary
--------------+-------------+---------
Hanako Suzuki | Engineering | 620000
Taro Yamada   | Sales       | 480000

-- Department rankings.
employee_name | department  | salary  | department_rank
--------------+-------------+---------+----------------
Hanako Suzuki | Engineering | 620000  | 1
Saburo Sato   | Engineering | 390000  | 2
Taro Yamada   | Sales       | 480000  | 1
Megumi Tanaka | Sales       | 320000  | 2

The correlated subquery syntax works across all major databases.

-- Works in MySQL, PostgreSQL, SQL Server, Oracle, and SQLite
SELECT emp.employee_name, emp.department, emp.salary
FROM employees AS emp
WHERE emp.salary > (
    SELECT AVG(sub.salary)
    FROM employees AS sub
    WHERE sub.department = emp.department
)
ORDER BY emp.department, emp.salary DESC;

In Oracle, the AS keyword cannot be used for table aliases. Omit AS when specifying a table alias.

-- Oracle: omit AS for table aliases
SELECT emp.employee_name, emp.department, emp.salary
FROM employees emp
WHERE emp.salary > (
    SELECT AVG(sub.salary)
    FROM employees sub
    WHERE sub.department = emp.department
)
ORDER BY emp.department, emp.salary DESC;

A correlated subquery executes once for every row of the outer query. If the outer query returns N rows, the subquery runs N times, so using a correlated subquery on large datasets can significantly degrade performance. The same result can often be rewritten using window functions (OVER / PARTITION BY) or a JOIN, and a rewrite is worth considering for large tables.

When combined with EXISTS, evaluation stops as soon as the subquery finds one matching row, making it more efficient than IN. A correlated subquery in the SELECT clause is easy to read but carries a higher execution cost, so use it with care.

For the basics of subqueries, see Subquery (scalar / row). For how to use EXISTS, see IN / EXISTS.