"Find the employees who earn more than the average salary of their own department." A plain subquery cannot answer this, because there is no single average to compare against: Sales has one average, IT another. The inner query has to know which department the outer row belongs to. When a subquery refers to a column of the outer query like this, it is a correlated subquery, and logically it is evaluated once for every row of the outer query.
Sample data
CREATE TABLE employees (emp_id INT PRIMARY KEY, name VARCHAR(20), dept VARCHAR(10), salary INT);
INSERT INTO employees VALUES
(1,'Anjali','Sales',52000), (2,'Bilal','Sales',61000), (3,'Charu','Sales',45000),
(4,'Deepak','IT',90000), (5,'Esha','IT',78000), (6,'Faisal','HR',50000);
CREATE TABLE leave_requests (req_id INT PRIMARY KEY, emp_id INT, leave_date DATE);
INSERT INTO leave_requests VALUES
(1,1,'2026-11-03'), (2,1,'2026-11-10'), (3,4,'2026-11-07'), (4,6,'2026-11-12');
Department averages for reference: Sales 52,666.67, IT 84,000, HR 50,000.
Example 1: compare each row with its own group
SELECT e.name, e.dept, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept = e.dept -- e.dept comes from the OUTER row
)
ORDER BY e.emp_id;
| name | dept | salary |
|---|---|---|
| Bilal | Sales | 61000 |
| Deepak | IT | 90000 |
Dry run, row by row
| Outer row | Inner query becomes | Inner result | salary > result? |
|---|---|---|---|
| Anjali, Sales, 52000 | AVG where dept = 'Sales' | 52666.67 | No |
| Bilal, Sales, 61000 | AVG where dept = 'Sales' | 52666.67 | Yes |
| Charu, Sales, 45000 | AVG where dept = 'Sales' | 52666.67 | No |
| Deepak, IT, 90000 | AVG where dept = 'IT' | 84000 | Yes |
| Esha, IT, 78000 | AVG where dept = 'IT' | 84000 | No |
| Faisal, HR, 50000 | AVG where dept = 'HR' | 50000 | No (equal, not greater) |
The two aliases matter: e is the outer copy of the table and e2 the inner copy. Without them, WHERE dept = dept would compare the inner row with itself and always be true.
Example 2: EXISTS and NOT EXISTS
The most common correlated subqueries in real code check whether related rows exist. Who has not applied for any leave this month?
SELECT e.name
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM leave_requests l WHERE l.emp_id = e.emp_id
)
ORDER BY e.emp_id;
| name |
|---|
| Bilal |
| Charu |
| Esha |
EXISTS only cares whether at least one row comes back, so SELECT 1 is the usual convention; the selected value is ignored.
Example 3: a correlated subquery in the SELECT list
SELECT e.name,
(SELECT MAX(l.leave_date) FROM leave_requests l WHERE l.emp_id = e.emp_id) AS last_leave
FROM employees e
ORDER BY e.emp_id;
| name | last_leave |
|---|---|
| Anjali | 2026-11-10 |
| Bilal | NULL |
| Charu | NULL |
| Deepak | 2026-11-07 |
| Esha | NULL |
| Faisal | 2026-11-12 |
A scalar subquery in the select list must return one value per outer row; if it could return two rows MySQL raises error 1242 "Subquery returns more than 1 row". An aggregate such as MAX() always returns exactly one value.
The NOT IN trap that NOT EXISTS avoids
Suppose one leave request was saved without an employee ID:
INSERT INTO leave_requests VALUES (5, NULL, '2026-11-15');
SELECT name FROM employees
WHERE emp_id NOT IN (SELECT emp_id FROM leave_requests);
Result: Empty set. Because the list now contains a NULL, emp_id NOT IN (1, 1, 4, 6, NULL) can never be true; it is either false or unknown. The NOT EXISTS query from Example 2 still returns Bilal, Charu and Esha, because it compares row by row and simply finds no match for them. Prefer NOT EXISTS whenever the inner column can be NULL. This behaviour is explained further in handling NULL values in MySQL.
Performance, and two rewrites
EXPLAIN on Example 1 shows the inner query as a DEPENDENT SUBQUERY, the plan label for "depends on the outer row":
| id | select_type | table | type | rows |
|---|---|---|---|---|
| 1 | PRIMARY | e | ALL | 6 |
| 2 | DEPENDENT SUBQUERY | e2 | ALL | 6 |
On six rows this costs nothing. On 2 lakh employees, recomputing a department average for every row is slow unless dept is indexed, and even then a single-pass rewrite is usually better. MySQL 8 often transforms EXISTS and IN subqueries into semi-joins automatically, but a correlated scalar subquery like Example 1 is a good candidate to rewrite by hand. Both versions below return the same two rows (Bilal and Deepak):
-- Rewrite A: join to a pre-aggregated derived table (works on any version)
SELECT e.name, e.dept, e.salary
FROM employees e
JOIN (SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept) d
ON d.dept = e.dept
WHERE e.salary > d.avg_sal
ORDER BY e.emp_id;
-- Rewrite B: window function (MySQL 8.0+)
SELECT name, dept, salary
FROM (SELECT emp_id, name, dept, salary,
AVG(salary) OVER (PARTITION BY dept) AS avg_sal
FROM employees) t
WHERE salary > avg_sal
ORDER BY emp_id;
Each computes the averages once instead of once per row. Measure with EXPLAIN ANALYZE on your own data before deciding; for small tables or highly selective indexed lookups, the correlated version can be perfectly fine and easier to read.
Common mistakes
- Forgetting the correlation condition, which turns the subquery into a plain one and compares against the company-wide average.
- Using
NOT INon a nullable column (see above). - Putting several correlated scalar subqueries in one select list, each scanning the same table. One
LEFT JOINto an aggregated derived table usually replaces them all.
Start with the basics in the MySQL subqueries tutorial, compare approaches in JOIN vs subquery performance, and learn the window-function rewrite in the window functions tutorial.