> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL correlated subqueries

> How a correlated subquery in MySQL reads a column from the outer row, why that makes it run once per row, and when the cost is worth paying.

<Card title="VillageSQL is a drop-in replacement for MySQL with extensions." icon="database" href="/docs/mysql-8.4/stable/quickstart">
  All examples on this page work on VillageSQL. Install Now →
</Card>

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

```sql theme={null}
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.

```sql theme={null}
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;
```

```text theme={null}
+------------------+--------+--------+
| 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](/docs/tutorial/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](/docs/guides/subqueries-vs-joins) 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.

## See also

* [Subqueries](/docs/tutorial/subqueries) — the uncorrelated kind, which runs once
* [EXISTS](/docs/tutorial/exists) — a correlated subquery used as a test rather than a value
* [Derived tables](/docs/tutorial/derived-tables) — the cheaper rewrite
* [Subqueries vs JOINs in MySQL](/docs/guides/subqueries-vs-joins) — reading the plan to decide