Subqueries By relationship correlated · EXISTS · per-row

Correlated Subquery

A subquery whose inner query refers to the outer row. Runs once per outer row and is the standard tool for per-row comparisons and EXISTS / NOT EXISTS patterns.

What a correlated subquery is

A regular subquery runs once. The database evaluates the inner query, hands the result to the outer query, and you're done.

A correlated subquery is different — its inner query references a column from the outer query, so the database has to re-evaluate it once for every outer row. The inner query depends on the row currently being considered by the outer query, which is why correlated subqueries are sometimes called repeating or synchronised subqueries.

How execution differs

Demo dataset — employees

SQL — Create & insert

CREATE TABLE employees (
    employee_id    INT PRIMARY KEY,
    first_name     VARCHAR(50),
    last_name      VARCHAR(50),
    email          VARCHAR(100),
    phone_number   VARCHAR(20),
    hire_date      DATE,
    job_id         INT,
    salary         DECIMAL(10, 2),
    manager_id     INT,
    department_id  INT
);

INSERT INTO employees VALUES
(100, 'Steven',    'King',      'steven.king@x.org',     '515.123.4567', '1987-06-17',  4, 24000.00, NULL,  9),
(101, 'Neena',     'Kochhar',   'neena.kochhar@x.org',   '515.123.4568', '1989-09-21',  5, 17000.00,  100,  9),
(115, 'Alexander', 'Khoo',      'alexander.khoo@x.org',  '515.127.4562', '1995-05-18', 13,  3100.00,  114,  3),
(116, 'Shelli',    'Baida',     'shelli.baida@x.org',    '515.127.4563', '1997-12-24', 13,  2900.00,  114,  3),
(200, 'Jennifer',  'Whalen',    'jennifer.whalen@x.org', '515.123.4444', '1987-09-17',  3,  4400.00,  101,  1),
(201, 'Michael',   'Hartstein', 'michael.h@x.org',       '515.123.5555', '1996-02-17', 10, 13000.00,  100,  2),
(202, 'Pat',       'Fay',       'pat.fay@x.org',         '603.123.6666', '1997-08-17', 11,  6000.00,  201,  2),
(203, 'Susan',     'Mavris',    'susan.mavris@x.org',    '515.123.7777', '1994-06-07',  8,  6500.00,  101,  4);

Example 1 — earners above their department's average

For each employee, compare their salary against the average salary of their own department. The inner query receives e.department_id from the outer row — that's the correlation.

SQL

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

Notice the alias e on the outer table and its reuse inside the subquery. That's what makes this correlated.

Example 2 — EXISTS pattern

Show employees who have at least one direct report. The inner query asks "is there any employee whose manager_id equals my id?"

SQL

SELECT e.employee_id, e.first_name
FROM   employees e
WHERE  EXISTS (
    SELECT 1
    FROM   employees r
    WHERE  r.manager_id = e.employee_id
);

EXISTS + a correlated subquery is the canonical way to ask "are there any related rows?" — and it handles NULLs correctly, unlike NOT IN.

Example 3 — NOT EXISTS for "no related rows"

The flip side: employees who have no direct reports.

SQL

SELECT e.employee_id, e.first_name
FROM   employees e
WHERE  NOT EXISTS (
    SELECT 1
    FROM   employees r
    WHERE  r.manager_id = e.employee_id
);

💡 Use NOT EXISTS instead of NOT IN when the inner query could contain NULLs. NOT EXISTS evaluates each outer row independently and behaves correctly even with NULLs in the data.

Example 4 — UPDATE driven by a correlated subquery

Correlated subqueries aren't only for SELECT. Here we cap each employee's salary at their department's maximum.

SQL

UPDATE employees e
SET    salary = (
    SELECT MAX(salary)
    FROM   employees
    WHERE  department_id = e.department_id
)
WHERE  e.salary < (
    SELECT AVG(salary)
    FROM   employees
    WHERE  department_id = e.department_id
);

Correlated vs regular subquery

Aspect	Regular	Correlated
Inner query runs	Once	Once per outer row
References outer columns	No	Yes
Performance	Generally faster	Slower — but the optimizer may rewrite it
Typical patterns	Filter by an aggregate value	Per-row comparisons, EXISTS / NOT EXISTS

⚠ Performance. A naïve correlated subquery on a million-row table can run a million inner queries. Modern optimizers often rewrite them as joins, but it's worth checking with EXPLAIN. When in doubt, see if a window function or a JOIN with GROUP BY does the job.

Recap

Correlated subqueries reference an outer column inside the inner query.
The inner query re-runs for each outer row.
Use them for "compare each row to something derived from related rows."
EXISTS / NOT EXISTS are the safest correlated patterns — NULL-safe and often optimizable.
Beware performance on large tables; consider window functions or joins as alternatives.