Correlated Subqueries: Comparing Each Row to Its Own Group
Find movies whose budget exceeds the average budget for their own genre, using a subquery that references the outer query's current row.
Overview
A correlated subquery is a subquery that references a column from the outer query, which means it gets re-evaluated once per outer row rather than computed a single time up front. This lets you compare each row to a computed value based on that specific row's own group — for example, "is this movie's budget above average for its own genre," where "its own genre" changes for every row.
This is a step beyond a plain (uncorrelated) subquery, and it's a common way
to express "compare this row to a summary of its peers" without needing a
GROUP BY that would collapse the individual rows away.
The Query
Loading database engine...
Step-by-Step Breakdown
FROM movies m— the outer query scans every movie, one row at a time.- The subquery
SELECT AVG(m2.budget_millions) FROM movies m2 WHERE m2.genre = m.genre— for the movie currently being evaluated, this computes the average budget across all movies sharing that same genre. Them2.genre = m.genrecondition is the correlation:m.genrecomes from the outer row. WHERE m.budget_millions > (...)— keeps only movies whose own budget is higher than their genre's average, computed fresh for each row.ORDER BY m.genre, m.budget_millions DESC— grouped visually by genre in the output, highest budget first within each genre.
Because the subquery is correlated, it effectively runs once per outer row
in the naive mental model (real query planners often optimize this), each
time with a different genre value substituted in.
Variations
Show every movie alongside its genre's average budget as a scalar subquery
in the SELECT list, instead of filtering:
Loading database engine...
Here the correlated subquery appears in the SELECT list rather than
WHERE, so instead of filtering rows out, it adds a computed column to
every row: each movie's own budget sits right next to its genre's average
for easy comparison.
A scalar subquery must return at most one row
Both examples rely on AVG() collapsing all matching rows into a single
number. If you wrote a correlated subquery in the SELECT list that could
return more than one row per outer row (say, individual budgets instead of
an aggregate), most databases will raise a runtime error rather than pick
one arbitrarily.
Common Mistakes
- Forgetting the correlation entirely. If the subquery said
WHERE m2.genre = 'Drama'(a fixed value) instead of= m.genre, every outer row would be compared to the same fixed average, not its own genre's average. - Confusing this with a
GROUP BYquery.GROUP BY genrewould give you one row per genre with the average, but you'd lose the individual movie rows. A correlated subquery lets you keep the row-level detail while still comparing against a group-level aggregate. - Performance blind spots on large tables. A naive correlated subquery conceptually re-runs once per outer row, which can be slow on large datasets; query planners often rewrite these into joins internally, but it's worth knowing the pattern can be expensive if the optimizer can't help.
- Off-by-one comparison direction.
>versus>=changes whether a movie sitting exactly at its genre's average is included — a common detail to get wrong when translating a business requirement like "above average" into SQL.