Correlated Subqueries: Row-by-Row Comparisons
Concept. A correlated subquery reads a value from the current outer row. A simple logical model on how it works: evaluate the inner query once per outer row (though the optimizer may rewrite it into a join or another equivalent plan.)
Intuition. In "users with a rating above their own average," the current user determines which rows contribute to the inner average.
Figure: Basic gives one global average; correlated gives each user their ownA Listens table grouped by user (Mickey blue, Minnie orange, Daffy purple) feeds two aggregations. A basic subquery sends all rows to one global average, 4.2, used for everyone. A correlated subquery sends each user's rows, along a colored arrow, to that user's own average: Mickey 4.2, Minnie 4.4, Daffy 3.9. The colored arrow is the correlation, WHERE user_id = u.user_id. Color key: blue Mickey, orange Minnie, purple Daffy; grey is the global path.
| name | rating |
|---|---|
| Mickey | 4.5 |
| Mickey | 4.2 |
| Mickey | 3.9 |
| Minnie | 4.7 |
| Minnie | 4.6 |
| Minnie | 3.9 |
| Daffy | 2.9 |
| Daffy | 4.9 |
| Daffy | NULL |
| AVG over all ratings |
|---|
| 4.2 |
| user | AVG of THEIR ratings |
|---|---|
| Mickey | 4.2 |
| Minnie | 4.4 |
| Daffy | 3.9 |
Figure 1. A basic subquery produces one global value. A correlated subquery uses a value from the current outer row, so Mickey compares to 4.2, Minnie to 4.4, and Daffy to 3.9. This is the logical model; the optimizer may use an equivalent physical plan.
Problem: Above Their Own Average
Which users have at least one rating above their own average?
For each user, collect their ratings and compute their average, ignoring NULL ratings. Keep the user if any of their ratings exceeds that average.
Each user supplies their own comparison value; one global average answers a different question.
Mental Model: A Double For-Loop
Use a correlated subquery when the comparison depends on the current row.
The inner query has its own scope (l2, its copy of Listens), but it reads u.user_id from the outer scope. That cross-reference is the correlation.
Figure: Intuition: trace the correlation with a double loopConceptual pseudocode, not a required execution plan: the outer loop runs once per user u; the inner SELECT AVG runs each pass over Listens l2 WHERE l2.user_id = u.user_id. An arrow links the inner reference u.user_id back to the outer loop variable u: that cross-reference is the correlation. Two scopes: u is the outer scope (current user), l2 is the inner scope (this query's Listens). The inner uses each user's ratings in this model: Mickey 4.2, Minnie 4.4, Daffy 3.9. The optimizer may choose an equivalent plan.
Figure 2. This double loop is how to trace the result by hand, not necessarily how the database executes it. It walks one user u at a time. The pink link marks the correlation: l2.user_id = u.user_id reads u from the outer scope, so the inner average uses only that user's rows.
Example: Per-User Averages
SELECT DISTINCT u.name
FROM Users u
JOIN Listens l ON l.user_id = u.user_id
WHERE l.rating > (
SELECT AVG(rating)
FROM Listens l2
WHERE l2.user_id = u.user_id
);
Figure: User ratings above their averageUser ratings above their average. One row per rating, grouped by user. The conceptual trace uses each user's ratings (the correlation). Rows that fail the comparison are struck out; the survivors give the output.
| user | rating | their average | above their average? |
|---|---|---|---|
| Mickey | 4.5 | 4.2 | ✓ yes |
| Mickey | 4.2 | 4.2 | ✗ no |
| Mickey | 3.9 | 4.2 | ✗ no |
| Minnie | 4.7 | 4.4 | ✓ yes |
| Minnie | 4.6 | 4.4 | ✓ yes |
| Minnie | 3.9 | 4.4 | ✗ no |
| Daffy | 2.9 | 3.9 | ✗ no |
| Daffy | 4.9 | 3.9 | ✓ yes |
| Daffy | NULL | 3.9 | ✗ no |
| Pluto | (no listens) | NULL | ✗ no |
Figure 3. The outer query joins each listen to its user. For each joined row, the inner query computes that user's average: Mickey gets 4.2, Minnie gets 4.4, and Daffy gets 3.9. The comparison keeps above-average ratings, and DISTINCT returns each surviving user once. Pluto has no listens, so the inner join excludes him before the comparison.
Example: Existence Checks (EXISTS and NOT EXISTS)
Without a reference to the outer row, EXISTS tests the same global condition for every row. Check that the inner WHERE refers to the intended outer alias.
Figure: NOT EXISTS: users with no rating above 4.5NOT EXISTS: users with no rating above 4.5. One row per rating, grouped by user. The conceptual trace uses each user's ratings (the correlation). Rows that fail the comparison are struck out; the survivors exclude their users from the NOT EXISTS output.
Figure 4. Cross out ratings that fail the inner WHERE. NOT EXISTS keeps users with no surviving rating: Mickey and Pluto. EXISTS would return Minnie and Daffy.
| user | rating |
|---|---|
| Minnie | 4.7 |
| Minnie | 4.6 |
| Daffy | 4.9 |
| name |
|---|
| Mickey |
| Pluto |
Repeated Inner Work
Figure: Repeated inner work in the logical modelThe direct logical model has one inner evaluation per outer row. For 10,000 users, that means 10,000 inner evaluations. The optimizer can rewrite the query into an equivalent join that computes every average in one pass: same answer, far less work.
Figure 5. In the direct logical plan, 10,000 outer rows can trigger 10,000 inner evaluations. We cover how databases optimize this in Module 3.
Takeaway
The outer row determines which inner rows contribute to the result. Check the outer-column reference to see what each inner query depends on.