MySQL correlated subqueries

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

An ordinary subquery runs once and hands back an answer. A correlated subquery names a column from the outer query, so it has a different answer for every outer row and the server has to run it again for each one. Open the client with mysql -u root -p sakila before you start.

How a correlated subquery works

SELECT COLUMN_LIST
FROM TABLE_NAME AS OUTER_ALIAS
WHERE COLUMN_NAME > (
  SELECT AGGREGATE_FUNCTION(COLUMN_NAME)
  FROM TABLE_NAME AS INNER_ALIAS
  WHERE INNER_ALIAS.COLUMN_NAME = OUTER_ALIAS.COLUMN_NAME
);

Replace COLUMN_LIST with the columns you want, TABLE_NAME with the table, COLUMN_NAME with the columns being compared, AGGREGATE_FUNCTION with the function the inner query runs, and OUTER_ALIAS and INNER_ALIAS with 2 names for the tables involved. The 2 aliases are what make it work: the inner query mentions the outer one by name, which is the correlation. Read it as a question asked once per outer row, with that row’s values filled in.

The question it answers

“Which films are longer than average” needs 1 average. “Which films are longer than average for their rating” needs a different average per row, and that is what a correlated subquery gives you.

SELECT f.title, f.rating, f.length
FROM film AS f
WHERE f.length > (SELECT AVG(f2.length) FROM film AS f2 WHERE f2.rating = f.rating)
ORDER BY f.rating, f.title
LIMIT 5;

+------------------+--------+--------+
| title            | rating | length |
+------------------+--------+--------+
| AFFAIR PREJUDICE | G      |    117 |
| AFRICAN EGG      | G      |    130 |
| ALAMO VIDEOTAPE  | G      |    126 |
...
+------------------+--------+--------+
5 rows in set (0.12 sec)

f2.rating = f.rating is the line that matters. It ties the inner query to the row being tested, so a G film is compared against the G average and an R film against the R average. Take that line out and the subquery averages the whole catalog, the same value every time, and the query answers a different question.

It runs once per row

Compare the timing above with the queries in the Subqueries lesson, which return too fast to measure. This one takes a tenth of a second on 1,000 rows, and your exact figure will differ, because the server evaluates the inner query per outer row rather than once. That is the whole trade. A correlated subquery is the clearest way to write “compare each row against its own group”, and the cost grows with the table. MySQL rewrites some correlated subqueries into joins by itself, and there are other shapes that answer the same question in 1 pass. Subqueries vs JOINs in MySQL covers both, and reading the plan that tells you which you got.

Summary

Use a correlated subquery to compare each row against something computed from its own group.
Give both tables aliases, because the inner query has to name the outer one.
Expect it to run once per outer row.
Rewrite it as a derived table and a join when the table is large.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How a correlated subquery works

The question it answers

It runs once per row

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How a correlated subquery works

​The question it answers

​It runs once per row

​Summary

​See also

How a correlated subquery works

The question it answers

It runs once per row

Summary

See also