A correlated subquery uses a column from the outer query. It cannot run on its own: it depends on the current outer row, and MySQL evaluates it for each row (in principle).
query.sql
SQL
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) NOT NULL,
manager_id INT NULL,
hired_on DATE NOT NULL
);
INSERT INTO employees (name, department, salary, manager_id, hired_on) VALUES
('Asha Rao', 'management', 150000.00, NULL, '2020-01-15'),
('Ben Carter', 'engineering', 120000.00, 1, '2021-03-01'),
('Chitra Nair', 'engineering', 95000.00, 2, '2022-07-11'),
('Dev Malhotra', 'engineering', 88000.00, 2, '2023-01-09'),
('Esha Gupta', 'sales', 70000.00, 1, '2021-09-20'),
('Farid Khan', 'sales', 64000.00, 5, '2022-11-02'),
('Gita Menon', 'sales', 64000.00, 5, '2024-02-14'),
('Hari Iyer', 'support', 52000.00, 1, '2023-05-30');Employees who earn more than their department’s average
query.sql
SQL
SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(x.salary) FROM employees x WHERE x.department = e.department)
ORDER BY e.department, e.salary DESC;
Output
OUTPUT
+------------+-------------+-----------+
| name | department | salary |
+------------+-------------+-----------+
| Ben Carter | engineering | 120000.00 |
| Esha Gupta | sales | 70000.00 |
+------------+-------------+-----------+
2 rows in setFor each employee e, the inner query averages the salaries of that employee’s department. The reference e.department ties the two together, so the “average” is different for each row. A normal subquery could only compare with one global average.
The same question as a join
query.sql
SQL
SELECT e.name, e.department, e.salary
FROM employees e
JOIN (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) a ON a.department = e.department
WHERE e.salary > a.avg_salary
ORDER BY e.department, e.salary DESC;
Output
OUTPUT
+------------+-------------+-----------+
| name | department | salary |
+------------+-------------+-----------+
| Ben Carter | engineering | 120000.00 |
| Esha Gupta | sales | 70000.00 |
+------------+-------------+-----------+
2 rows in setHere the averages are calculated once per department instead of once per employee. For big tables that is a big gain.
Or with a window function
query.sql
SQL
SELECT name, department, salary
FROM (
SELECT name, department, salary, AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees
) AS t
WHERE salary > avg_salary
ORDER BY department, salary DESC;
Output
OUTPUT
+------------+-------------+-----------+
| name | department | salary |
+------------+-------------+-----------+
| Ben Carter | engineering | 120000.00 |
| Esha Gupta | sales | 70000.00 |
+------------+-------------+-----------+
2 rows in setAll three give the same result. The window version is often the simplest to read in MySQL 8.
The latest row per group
“The most recently hired employee in each department”:
query.sql
SQL
SELECT e.name, e.department, e.hired_on
FROM employees e
WHERE e.hired_on = (SELECT MAX(x.hired_on) FROM employees x WHERE x.department = e.department)
ORDER BY e.department;
Output
OUTPUT
+--------------+-------------+------------+
| name | department | hired_on |
+--------------+-------------+------------+
| Dev Malhotra | engineering | 2023-01-09 |
| Asha Rao | management | 2020-01-15 |
| Gita Menon | sales | 2024-02-14 |
| Hari Iyer | support | 2023-05-30 |
+--------------+-------------+------------+
4 rows in setCounting related rows
query.sql
SQL
SELECT m.name, (SELECT COUNT(*) FROM employees r WHERE r.manager_id = m.id) AS direct_reports
FROM employees m
ORDER BY direct_reports DESC, m.name
LIMIT 3;
Output
OUTPUT
+------------+----------------+
| name | direct_reports |
+------------+----------------+
| Asha Rao | 3 |
| Ben Carter | 2 |
| Esha Gupta | 2 |
+------------+----------------+
3 rows in setCorrelated subqueries with EXISTS
The most natural use of correlation is EXISTS (EXISTS):
query.sql
SQL
SELECT m.name FROM employees m WHERE EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = m.id) ORDER BY m.id;
Output
OUTPUT
+------------+
| name |
+------------+
| Asha Rao |
| Ben Carter |
| Esha Gupta |
+------------+
3 rows in setPerformance
- A correlated subquery may run once per outer row. With 1 million outer rows, that is a million executions.
- It is fast when the inner query uses an index on the correlating column (
manager_id,department): each execution is a small index lookup. - MySQL 8 can decorrelate some forms: it converts
EXISTSandINinto semi-joins, and some scalar subqueries into derived tables. CheckEXPLAINto see what actually happens. - If it is slow, try a join to a pre-aggregated derived table, or a window function.
query.sql
SQL
CREATE INDEX idx_dept ON employees (department);
EXPLAIN FORMAT=TRADITIONAL SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(x.salary) FROM employees x WHERE x.department = e.department);
Output
OUTPUT
+----+--------------------+-------+------------+------+---------------+----------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+------+---------------+----------+---------+--------------------+------+----------+-------------+
| 1 | PRIMARY | e | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | x | NULL | ref | idx_dept | idx_dept | 202 | learn.e.department | 2 | 100.00 | NULL |
+----+--------------------+-------+------------+------+---------------+----------+---------+--------------------+------+----------+-------------+
2 rows in setIn the plan, the select type DEPENDENT SUBQUERY is the sign of a correlated subquery.
Alias discipline
In a correlated subquery, always use table aliases and prefix the columns. Writing WHERE department = department compares the inner column with itself and is always true. A subtle and common bug:
query.sql
SQL
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = department);
Output
OUTPUT
+--------------+
| name |
+--------------+
| Asha Rao |
| Ben Carter |
| Chitra Nair |
| Dev Malhotra |
+--------------+
4 rows in setInside the subquery, department means the inner table’s column on both sides, so the inner query averages all employees. The result is the same as the uncorrelated query, not the per-department one.
warning
Forgetting the alias
A condition without the outer alias is not correlated at all. Always write
x.department = e.department.