SQL Tutorial › Subqueries & CTEs › Correlated Subqueries

SQL Correlated Subqueries

AdvancedLesson 41 of 6910 min read6 queries run on MySQL 8.4

A correlated subquery refers to a column of the outer query, so it cannot run on its own: conceptually it is re-run for every outer row. That makes it the natural way to ask row-by-row questions such as “is this employee the best paid in their own department?” and “what is this customer’s latest order?”.

In this lesson

Recognise a correlated subquery
Trace how it runs row by row
Solve “greatest per group” problems
Rewrite with joins or window functions for speed

Sample data

employees · 12 rows (some columns)

emp_id	first_name	dept_id	salary
100	Meera	NULL	250000
101	Asha	1	125000
102	Rahul	1	98000
103	Priya	1	72000
104	Karan	1	72000
105	Vikram	2	105000
106	Neha	2	54000
107	Arjun	2	48000
108	Kavya	3	88000
109	Rohan	3	45000
110	Sneha	4	78000
111	Amit	1	25000

Correlated vs non-correlated

	Non-correlated	Correlated
Refers to the outer query	no	yes
Can run on its own	yes	no
Runs	once	conceptually once per outer row

Above their department’s average

SQL

SELECT e.first_name, e.dept_id, e.salaryFROM employees eWHERE e.salary > (  SELECT AVG(x.salary)  FROM employees x  WHERE x.dept_id = e.dept_id)ORDER BY e.dept_id, e.salary DESC;

ResultMySQL 8.4

first_name	dept_id	salary
Asha	1	125000
Rahul	1	98000
Vikram	2	105000
Kavya	3	88000

4 rows in set

How this query works, step by step

MySQL takes the first outer row, say Asha (dept 1, 125,000).
The inner query runs with e.dept_id = 1: the Engineering average is 78,400.
125,000 > 78,400, so Asha is kept.
Next row, Priya (dept 1, 72,000): same average, 72,000 is not higher, so she is dropped.
This repeats for every employee, each compared with their own department. The CEO’s department is NULL, so x.dept_id = NULL matches nothing, the average is NULL and she is dropped.

Greatest per group

The top earner in each department:

SQL

SELECT e.dept_id, e.first_name, e.salaryFROM employees eWHERE e.salary = (  SELECT MAX(x.salary)  FROM employees x  WHERE x.dept_id = e.dept_id)ORDER BY e.dept_id;

ResultMySQL 8.4

dept_id	first_name	salary
1	Asha	125000
2	Vikram	105000
3	Kavya	88000
4	Sneha	78000

4 rows in set

How this query works, step by step

For each employee, the subquery finds the maximum salary of their department.
The employee is kept only if they earn exactly that maximum.
If two people tied for the top, both would be returned.

Latest order of each customer

SQL

SELECT o.customer_id, o.order_id, o.order_date, o.statusFROM orders oWHERE o.order_date = (  SELECT MAX(o2.order_date)  FROM orders o2  WHERE o2.customer_id = o.customer_id)ORDER BY o.customer_id;

ResultMySQL 8.4

customer_id	order_id	order_date	status
1	1008	2025-04-28	Pending
2	1007	2025-04-10	Delivered
3	1009	2025-05-15	Delivered
4	1005	2025-03-08	Delivered
5	1010	2025-06-01	Delivered

5 rows in set

Correlated subquery in SELECT

Number of orders next to each customer:

SQL

SELECT c.customer_name,       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS ordersFROM customers cORDER BY orders DESC, c.customer_name;

ResultMySQL 8.4

customer_name	orders
Ananya Sharma	3
Fatima Khan	2
Ishita Bose	2
Rohit Kumar	2
David Fernandes	1
Manoj Pillai	0
Sara Thomas	0

7 rows in set

How this query works, step by step

For each customer row, the subquery counts that customer’s orders.
Customers without orders get 0 (COUNT of an empty set), unlike an inner join, which would drop them.

Correlated UPDATE

A correlated subquery can also supply the new value in an UPDATE. Copy the products into a product_sales table, then fill in each product’s total units ordered:

MySQL

CREATE TABLE product_sales AS SELECT product_id, product_name, 0 AS units FROM products;UPDATE product_sales psSET units = (SELECT COALESCE(SUM(oi.quantity), 0) FROM order_items oi WHERE oi.product_id = ps.product_id);SELECT * FROM product_sales ORDER BY units DESC, product_id;

ResultMySQL 8.4

mysql> CREATE TABLE product_sales AS SELECT product_id, product_name, 0 AS un…

Query OK, 9 rows affected

mysql> UPDATE product_sales ps

Query OK, 8 rows affected

mysql> SELECT * FROM product_sales ORDER BY units DESC, product_id

product_id	product_name	units
6	Notebook Pack	18
7	Gel Pens	6
2	Mechanical Keyboard	3
4	Office Chair	3
8	Desk Lamp	3
1	Wireless Mouse	2
3	USB-C Hub	2
5	Standing Desk	1
9	Monitor Arm	0

9 rows in set

Performance and alternatives

Running a query once per row sounds slow, and on large tables it can be. MySQL 8’s optimizer often rewrites correlated subqueries into joins automatically, and an index on the correlated column (dept_id, customer_id) helps a lot. Two common rewrites:

Join to a pre-aggregated derived table:

SQL

SELECT e.dept_id, e.first_name, e.salaryFROM employees eJOIN (SELECT dept_id, MAX(salary) AS max_salary FROM employees GROUP BY dept_id) m  ON m.dept_id = e.dept_id AND m.max_salary = e.salaryORDER BY e.dept_id;

ResultMySQL 8.4

dept_id	first_name	salary
1	Asha	125000
2	Vikram	105000
3	Kavya	88000
4	Sneha	78000

4 rows in set

Or a window function, covered in Top N per Group.

Exercise: Most expensive product per category

List the most expensive product of each category using a correlated subquery.

Expected resultMySQL 8.4

category	product_name	price
Electronics	Mechanical Keyboard	3499
Furniture	Standing Desk	18999
Stationery	Notebook Pack	249

3 rows in set

Hint 1

Compare price with the MAX(price) of the same category.

SQL

SELECT p.category, p.product_name, p.priceFROM products pWHERE p.price = (SELECT MAX(x.price) FROM products x WHERE x.category = p.category)ORDER BY p.category;

ResultMySQL 8.4

category	product_name	price
Electronics	Mechanical Keyboard	3499
Furniture	Standing Desk	18999
Stationery	Notebook Pack	249

3 rows in set

Quick quiz

1. What makes a subquery correlated?

2. Can a correlated subquery be run on its own?

Without the outer query, e.dept_id is unknown.

3. What usually makes correlated subqueries fast on big tables?

Correlated subquery FAQ

What is a correlated subquery?

A subquery that uses values from the outer query, so it is evaluated for each row of the outer query.

What is the difference between a correlated and a nested subquery?

A plain nested subquery is independent and runs once. A correlated subquery depends on the outer row and conceptually runs once per row.

Are correlated subqueries slow?

They can be on large tables without indexes. MySQL often optimizes them into joins; window functions or joins to aggregated derived tables are common faster alternatives.