Correlated Subqueries: Row-by-Row Comparisons

Concept. A correlated subquery reads at least one column from the outer query, so it executes once per outer row.

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. A regular subquery produces one global value and the outer query compares every row against that value. A correlated subquery executes the inner query once per outer row with the outer row's values as parameters. Mickey compares to 4.2, Minnie compares to 4.4, Daffy compares to 3.9.

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. The outer query selects a user. The inner query filters to that user's rows and computes the user's average rating. Mickey gets 4.2, Minnie gets 4.4, Daffy gets 3.9. Pluto has no ratings, so the inner query produces no average and the outer condition fails for Pluto.

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. The outer query runs once and the inner query runs once per user, so the plan executes 1 + N inner evaluations. 10,000 users produce 10,001 evaluations. Many optimizers recognize common correlated patterns and rewrite them as a single JOIN with a windowed aggregate.

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