Correlated Subqueries: Row-by-Row Comparisons

Concept. A correlated subquery references a column from the outer query, so it re-runs once per outer row instead of once for the whole query.

Intuition. "Users with a rating above their own average" recomputes each user's average for every row, because the comparison value depends on which user the row belongs to.

A regular subquery runs once and compares every user to one global average of 4.2; a correlated subquery re-runs its inner query once per outer row, so Mickey is measured against 4.2, Minnie against 4.4, Daffy against 3.9. The comparison value is recomputed for each row.

Figure 1. The difference in one picture. A regular subquery computes one global number and compares every row against it. A correlated subquery re-runs its inner query once per outer row, parameterized by that row: it measures Mickey against 4.2, Minnie against 4.4, Daffy against 3.9. The comparison value is recomputed for each row, the double-for-loop model below.

Example: Users Above Their Own Average

Users above their own average: the query runs the inner AVG once per user (the correlation, WHERE l2.user_id = u.user_id). Mickey averages 4.2 and has 4.5 above it; Minnie 4.4 with 4.7; Daffy 3.9 with 4.9; all kept. Pluto has no ratings, so no average and nothing to beat, dropped. Output: Mickey, Minnie, Daffy.

Figure 2. Each user is measured against their own average. The inner query re-runs filtered to that user (the highlighted correlation line), giving Mickey 4.2, Minnie 4.4, Daffy 3.9. Everyone has at least one rating above their own average, so all three are kept; Pluto has no ratings, no average, and drops.

Example: Existence Checks (EXISTS and NOT EXISTS)

EXISTS and NOT EXISTS for any rating above 4.5: per user the inner query asks if any rating exceeds 4.5. Mickey no, Minnie yes (4.7), Daffy yes (4.9), Pluto no. NOT EXISTS keeps the users with none: Mickey and Pluto. EXISTS keeps the opposite: Minnie and Daffy.

Figure 3. One correlated check answers both EXISTS and NOT EXISTS. For each user the inner query asks whether any rating beats 4.5. NOT EXISTS keeps the users with none (Mickey, whose top is exactly 4.5, and Pluto, who has no listens at all); EXISTS keeps the opposite pair, Minnie and Daffy.

Mental Model: A Double For-Loop

The correlation: the inner query references a variable scoped to the outer row, so the database runs it once per outer row. The inner query has its own scope (here l2, its own copy of Listens), but it reaches out to a variable from the outer scope (u). That cross-reference is the correlation.

A correlated subquery is a double for-loop: 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, which is the correlation. u is the outer scope (current user), l2 is the inner scope (this query's Listens). The inner re-runs per user: Mickey 4.2, Minnie 4.4, Daffy 3.9.

Figure 4. The execution model. The outer loop walks one user u at a time; the inner query re-runs each pass. The correlation is the pink link: inside the inner query, l2.user_id = u.user_id reads u from the outer scope. So the inner average is computed only over that user's rows, and the inner query runs once per outer row.

When to reach for this: whenever the comparison threshold changes per row. The clue is the word their or its own: "above their own average," "top in their genre," "better than their manager's salary." Each row supplies its own comparison value. If the threshold is a single global number, use a regular subquery; if it depends on the row, correlated.

Correlated vs Regular: Key Differences

Aspect Regular Subquery Correlated Subquery
Execution Runs once total Runs once per outer row
Independence Standalone query Depends on outer query
Use Case Global comparisons Row-specific comparisons
Performance Usually faster N+1 query problem
Example "Above global average" "Above your average"

When to Use Correlated Subqueries

Good Use Cases:

  • Personal comparisons (above your average)

  • Relative rankings (best in your category)

  • Row-specific existence checks (do you have...)

  • Self-referential queries (employees earning more than their manager)

Avoid When:

  • Simple global comparisons suffice

  • Performance is critical (consider JOIN alternatives)

  • Query complexity becomes unreadable

Common Patterns

  1. Personal Aggregates: Compare to your own AVG/MAX/MIN

  2. Existence per Entity: EXISTS with correlation

  3. Ranking within Groups: Find top N per category

  4. Self-Joins Alternative: often easier to read than the equivalent self-join.

Common Mistakes with Correlated Subqueries

Forgetting Correlation

Problem: Without correlation, EXISTS checks globally instead of per-row

Performance Anti-Pattern (N+1 Problem)

The N+1 problem: the outer query runs once over Users, then the inner query re-runs once per user, so 1 outer plus N inner equals N+1 queries, 10,001 for 10,000 users. The optimizer can rewrite it as a single JOIN with a windowed average, one pass over the data, just 1 query.

Figure 5. Running the inner query once per outer row costs 1 + N queries: 10,001 round-trips for 10,000 users, which scales linearly with the number of users and is far too slow on a large table. Modern optimizers (Postgres, MySQL, SQL Server) recognize the pattern and rewrite it as a single JOIN with a windowed aggregate, one pass over the data.

Note: Explicit JOINs are still preferred when you want clarity and guaranteed performance, rather than relying on the optimizer to spot the rewrite.