Correlated subqueries and derived tables

Correlated: the inner query depends on the outer row

In topic 1, (SELECT AVG(salary) FROM employees) gave the same number for every row. Now the question is "who earns more than the average of their own department?". The average depends on which employee we are looking at. The subquery must use the outer row's dept_id. A subquery that refers to a column of the outer query is called correlated.

SELECT e.name, e.dept_id, e.salary
FROM employees AS e
WHERE e.salary > (SELECT AVG(e2.salary)
                  FROM employees AS e2
                  WHERE e2.dept_id = e.dept_id);
-- Meera Iyer  | 1 | 250000   (Engineering average 140000)
-- Pooja Gupta | 2 | 110000   (Sales average 80666.67)
-- Farhan Ali  | 3 | 105000   (Finance average 86500)

Notice two aliases on the same table, as in a self join. Without them, WHERE dept_id = dept_id would compare the inner row with itself and always be true, giving the company average again.

What about Deepak, whose dept_id is NULL? Inside the subquery, e2.dept_id = NULL matches nothing, the average is NULL, and 45000 > NULL is unknown, so he is not returned. That is reasonable here, but always think about NULL keys.

Latest order per customer

SELECT c.name, o.id, o.order_date
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.order_date = (SELECT MAX(o2.order_date)
                      FROM orders AS o2
                      WHERE o2.customer_id = o.customer_id);
-- Asha Verma | 108 | 2025-04-02
-- Ravi Kumar | 106 | 2025-03-15
-- ... one row per customer who ordered

Derived tables: a subquery in FROM

A subquery in FROM returns a whole table, which the outer query reads like any other table. It is called a derived table (or an inline view). It needs an alias so the outer query can refer to its columns. MySQL requires the alias ("Every derived table must have its own alias"), and so did PostgreSQL before version 16, so always write one.

Derived tables answer "aggregate of an aggregate" questions. "What is the average amount a customer has spent?" First total per customer, then the average of those totals. You cannot write AVG(SUM(...)) in one step, but you can nest the steps.

SELECT ROUND(AVG(total), 2) AS avg_total
FROM (SELECT o.customer_id, SUM(oi.qty * oi.price) AS total
      FROM orders AS o
      JOIN order_items AS oi ON oi.order_id = o.id
      WHERE o.status <> 'Cancelled'
      GROUP BY o.customer_id) AS t;
-- 3461   (totals 4678, 6497, 1770 and 899)

You met a derived table in module 5, where aggregating each table first fixed fan-out. Any time you think "first make this small table, then query it", a derived table (or the CTE of the next topic) is the tool.

Correlated subqueries in SELECT

SELECT d.name,
       (SELECT COUNT(*) FROM employees AS e WHERE e.dept_id = d.id) AS headcount
FROM departments AS d;
-- Engineering 4, Sales 3, Finance 2, HR 1, Legal 0

A note on performance

Logically, a correlated subquery runs once per outer row. On 11 employees that is nothing; on 10 lakh orders it can be slow if the inner query has to scan the whole table each time. Modern planners often rewrite correlated subqueries into joins, and an index on the linking column (module 10) makes each lookup fast. If a query is slow, compare it with a join to a grouped derived table, or with a window function (module 7).

Common mistakes

• Forgetting the inner alias, so the "correlation" compares the inner table with itself. Give the inner copy its own alias (e2) and write e2.dept_id = e.dept_id.
• Leaving out the alias of a derived table. MySQL rejects it; write ) AS t.
• Trying AVG(SUM(x)). Nesting aggregates needs a derived table or a CTE.
• Using a correlated subquery on a huge table with no index on the linking column. Check the query plan or rewrite as a join.