After this lesson you can write a subquery that depends on the row the outer query is looking at. That unlocks questions such as "who earns more than the average of their own department?", which a plain subquery cannot answer.
Plain versus correlated
In the previous lesson the inner queries stood on their own. You could copy (SELECT AVG(salary) FROM employees) out and run it by itself. A correlated subquery cannot. It mentions a column from the outer query, so it only makes sense relative to the current outer row.
Consider "employees earning more than the average of their own department". The average is different for every department, so one fixed number will not do:
SELECT e.first_name, e.department_id, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(x.salary)
FROM employees x
WHERE x.department_id = e.department_id
)
ORDER BY e.department_id, e.salary DESC;+------------+---------------+--------+
| first_name | department_id | salary |
+------------+---------------+--------+
| Meera | 1 | 180000 |
| Arjun | 1 | 125000 |
| Kavya | 1 | 125000 |
| Vikram | 2 | 150000 |
| Neha | 2 | 90000 |
| Sameer | 2 | 90000 |
| Dev | 3 | 110000 |
| Lata | 4 | 115000 |
| Pooja | 5 | 85000 |
+------------+---------------+--------+Read it as a loop. For each employee e, the inner query averages the salaries of everyone in the same department, x.department_id = e.department_id, and the outer WHERE keeps the employee if their salary beats that average. The aliases matter: x is a second copy of the same table, and e is the outer one. Without separate aliases the inner department_id = department_id would simply compare a column with itself.
Two things in the output are worth noticing. Ravi (18) is missing, and so is anyone at or below their own department's average. Ravi has no department, so x.department_id = e.department_id is a comparison with NULL, no rows match, the average is NULL, and salary > NULL is not true. The query is not wrong, but his case is silently excluded.
The latest row per group
Another classic: the most recently hired employee in each department. For each employee, check that nobody in the same department was hired later:
SELECT e.first_name, e.department_id, e.hire_date
FROM employees e
WHERE e.hire_date = (
SELECT MAX(x.hire_date)
FROM employees x
WHERE x.department_id = e.department_id
)
ORDER BY e.department_id;+------------+---------------+------------+
| first_name | department_id | hire_date |
+------------+---------------+------------+
| Diya | 1 | 2024-06-10 |
| Aman | 2 | 2024-03-18 |
| Yash | 3 | 2022-05-30 |
| Omar | 4 | 2020-11-09 |
| Sana | 5 | 2023-07-17 |
+------------+---------------+------------+The inner query finds the newest hire date in the current employee's department. The outer query keeps the person whose own date equals it. If two people shared the latest date, both would be returned, so decide whether that is acceptable. Again Ravi is absent, for the same NULL reason.
A correlated subquery in SELECT
A correlated subquery can also sit in the SELECT list, giving each outer row one computed value. How many employees and how many projects does each department have?
SELECT d.name AS department,
(SELECT COUNT(*) FROM employees e WHERE e.department_id = d.id) AS employees,
(SELECT COUNT(*) FROM projects p WHERE p.department_id = d.id) AS projects
FROM departments d
ORDER BY d.id;+-------------+-----------+----------+
| department | employees | projects |
+-------------+-----------+----------+
| Engineering | 6 | 2 |
| Sales | 5 | 1 |
| Marketing | 3 | 1 |
| Finance | 2 | 1 |
| Support | 3 | 0 |
| Research | 0 | 0 |
+-------------+-----------+----------+This avoids the row multiplication problem from the lesson on joining several tables, because each count is computed on its own. And because COUNT over no rows is 0, Research simply shows 0 and 0. If you used SUM or MAX in such a subquery, a department with no rows would show NULL instead.
The subquery must return exactly one value for each row: one column, at most one row. A subquery returning two columns or two rows here is an error.
Comparing a row with its own group
You can also compare a row with other rows of its own table. For each assignment, show the hours alongside the highest hours that same employee logged on any project:
SELECT a.employee_id, a.project_id, a.hours,
(SELECT MAX(b.hours) FROM assignments b WHERE b.employee_id = a.employee_id) AS most_hours_on_one_project
FROM assignments a
WHERE a.employee_id IN (2, 4)
ORDER BY a.employee_id, a.project_id;+-------------+------------+-------+---------------------------+
| employee_id | project_id | hours | most_hours_on_one_project |
+-------------+------------+-------+---------------------------+
| 2 | 1 | 320 | 320 |
| 2 | 2 | 200 | 320 |
| 4 | 1 | 410 | 410 |
| 4 | 5 | 40 | 410 |
+-------------+------------+-------+---------------------------+The same figure repeats for each of the employee's rows, since it is a fact about the employee, not the row.
The cost, and the alternatives
A correlated subquery is, in concept, run once for every outer row. Modern database engines are good at rewriting many of them into joins, so they are not automatically slow. But on a large table, with no help from an index on the correlating column, the cost can grow quickly. Alternatives that you can choose between:
- Join to a summarised table. Compute the department averages once in a derived table, then join. It does the aggregation once.
- Window functions.
AVG(salary) OVER (PARTITION BY department_id)gives the same department average on each row without a subquery. They are the next section of this course. - EXISTS. When you only need to know whether a related row exists,
EXISTSis clearer and can stop at the first match.
Here is the first example rewritten as a join to a summarised table. It returns the same rows:
SELECT e.first_name, e.department_id, e.salary
FROM employees e
INNER JOIN (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) AS s ON s.department_id = e.department_id
WHERE e.salary > s.avg_salary
ORDER BY e.department_id, e.salary DESC;+------------+---------------+--------+
| first_name | department_id | salary |
+------------+---------------+--------+
| Meera | 1 | 180000 |
| Arjun | 1 | 125000 |
| Kavya | 1 | 125000 |
| Vikram | 2 | 150000 |
| Neha | 2 | 90000 |
| Sameer | 2 | 90000 |
| Dev | 3 | 110000 |
| Lata | 4 | 115000 |
| Pooja | 5 | 85000 |
+------------+---------------+--------+Neither version is better in every case. Start with the one you can read most easily, and measure only if the query is slow.
Differences between databases
Correlated subqueries are standard and work in MySQL, PostgreSQL, SQLite and SQL Server. The syntax above runs unchanged in all four. SQL Server and MySQL need a name on subqueries in FROM, as in the join version. For MySQL's treatment of subqueries in general, see Subqueries in MySQL.
Common mistakes
- Using the same alias for inner and outer tables. Name them differently, such as
eandx. - Forgetting the correlation condition. Without
x.department_id = e.department_idthe inner query is just a company-wide value, and your department logic is gone. - Returning several rows from a scalar subquery. In
SELECTor after=, the inner query must give one value. - Ignoring NULL in the correlating column. Rows with
NULLthere never match, and fall out of the result without warning. - Assuming it is fast. It can be, but check the plan and use an index on the correlating column.
Exercise
For each project, show its name, its budget, and the total hours logged on it using a correlated subquery on assignments. Then list the employees who log more hours on one project than the average hours per assignment for that same project.
Show one possible answer
SELECT p.name, p.budget,
(SELECT SUM(a.hours) FROM assignments a WHERE a.project_id = p.id) AS total_hours
FROM projects p
ORDER BY p.id;
SELECT a.employee_id, a.project_id, a.hours
FROM assignments a
WHERE a.hours > (
SELECT AVG(b.hours) FROM assignments b WHERE b.project_id = a.project_id
)
ORDER BY a.project_id, a.hours DESC;+-------------------+--------+-------------+
| name | budget | total_hours |
+-------------------+--------+-------------+
| Customer Portal | 400000 | 1160 |
| Mobile App | 650000 | 1100 |
| Spring Campaign | 120000 | 480 |
| Q3 Sales Push | 200000 | 1050 |
| Billing Migration | 300000 | 390 |
+-------------------+--------+-------------+
+-------------+------------+-------+
| employee_id | project_id | hours |
+-------------+------------+-------+
| 4 | 1 | 410 |
| 2 | 1 | 320 |
| 3 | 2 | 360 |
| 5 | 2 | 300 |
| 11 | 3 | 210 |
| 12 | 3 | 190 |
| 7 | 4 | 340 |
| 8 | 4 | 310 |
| 9 | 4 | 220 |
| 14 | 5 | 260 |
+-------------+------------+-------+Key points
- A correlated subquery refers to a column of the outer query, so it is conceptually re-evaluated for each outer row.
- Use it for questions that compare a row with a group it belongs to: above the average of their own department, the latest row per group.
- Give the outer and inner tables different aliases, or the same table name will confuse you and the database.
- A correlated subquery in SELECT returns one value per row, for example a count of related rows, and gives 0 or NULL for rows with no matches.
- On large tables a join with a pre-aggregated table, or a window function, is usually faster. Measure on your own data.