Hey everyone! 👋
It's been a while since I posted here. Over the past few days, I've been revisiting SQL from the ground up—not just solving problems, but understanding why different SQL concepts exist and when they should be used.
One topic that confused me (and many beginners) was correlated subqueries.
You may have seen queries like this:
SELECT ...
FROM table1 t1
WHERE EXISTS (
SELECT *
FROM table2 t2
WHERE t2.id = t1.id
);
Enter fullscreen mode Exit fullscreen mode
and wondered:
How is the inner query accessing columns from the outer query?
When does the inner query execute?
How is this different from a normal subquery?
In this article, we'll answer all of these questions by solving a real SQL problem from LeetCode.
By the end, you'll understand:
- ✅ What correlated subqueries are
- ✅ How they work internally
- ✅ SQL's logical execution order
- ✅ When to use them
- ✅ How they compare with window functions
Let's dive in!
Prerequisites
This article assumes you know:
- Basic
SELECTstatementsWHEREGROUP BY- Aggregate functions (
MIN,MAX,COUNT, ...)If you're comfortable with these, you're ready to learn correlated subqueries.
Table of Contents
- Why Are Correlated Subqueries So Confusing?
- Correlated Subqueries from Scratch
- Correlated vs Non-Correlated Subqueries
- SQL's Logical Execution Order (and Where Correlated Subqueries Fit)
- Using LeetCode 3421 as Our Running Example
- Before We Write SQL...
- My Fully Annotated Solution
- Walking Through the Query Step by Step Using Real Data
- Performance Discussion: Correlated Subqueries vs Window Functions
- Conclusion & Practice Problems
1. Why Are Correlated Subqueries So Confusing?
When I first started learning SQL, correlated subqueries felt like magic.
Questions like these constantly came to mind:
- How can the inner query access columns from the outer query?
- When does the inner query execute?
- Does it run once or multiple times?
- Why can't I execute it by itself?
If you've asked yourself any of these questions, don't worry—you'll have answers to all of them by the end of this article.
What is a subquery?
A subquery is just a SELECT statement nested inside another SQL statement. Nothing scary yet:
SELECT name, salary
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
Enter fullscreen mode Exit fullscreen mode
Here, the inner query (SELECT AVG(salary) FROM Employees) runs once, produces a single number (say, 55000), and the outer query just uses that number. The inner query doesn't care what row the outer query is currently looking at. This is a non-correlated (or "simple") subquery — it's fully independent and could be run on its own.
What makes a subquery "correlated"?
💡 Key Idea
A correlated subquery depends on values from the outer query.
Because of that, it cannot execute independently — it re-executes once per row of the outer query, using that row's values each time.
SELECT e.name, e.salary, e.department_id
FROM Employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM Employees e2
WHERE e2.department_id = e.department_id -- 👈 references outer row `e`
);
Enter fullscreen mode Exit fullscreen mode
Read that inner query in isolation:
SELECT AVG(salary) FROM Employees e2 WHERE e2.department_id = e.department_id
Enter fullscreen mode Exit fullscreen mode
e.department_id doesn't exist inside this subquery's own FROM Employees e2 — it's borrowed from the outer query. That's the defining trait. Mentally, think of it like a function with a parameter:
find_avg_salary_for(department_id):
return AVG(salary) WHERE department_id = department_id
Enter fullscreen mode Exit fullscreen mode
And SQL calls that "function" once for every single row the outer query touches, plugging in that row's department_id. That's the whole concept. Everything else is just decoration on top of this idea.
A tiny mental model
Think of the outer query as a for loop, and the correlated subquery as code that runs inside that loop, using the loop variable:
# This is NOT real SQL — just a mental model
for row in Employees:
dept_avg = AVG(salary WHERE department_id == row.department_id)
if row.salary > dept_avg:
emit(row.name, row.salary, row.department_id)
Enter fullscreen mode Exit fullscreen mode
This mental model is exactly why correlated subqueries can get expensive — because that's genuinely close to how the database may end up executing it if it can't optimize it away (more on that in the performance section).
Summary
A correlated subquery executes once for every row (or group) produced by the outer query, using that row's values as its input.
| Non-Correlated Subquery | Correlated Subquery | |
|---|---|---|
| References outer query? | ❌ No | ✅ Yes |
| Can run standalone? | ✅ Yes | ❌ No |
| Execution frequency | Once, total | Once per outer row (conceptually) |
| Typical use | Compare against a global value (e.g. overall average) | Compare against a value scoped to that row's group (e.g. that department's average, that student's max date) |
Non-correlated example — "Employees who earn more than the company-wide average":
SELECT name FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
Enter fullscreen mode Exit fullscreen mode
Correlated example — "Employees who earn more than their own department's average":
SELECT e.name FROM Employees e
WHERE e.salary > (
SELECT AVG(salary) FROM Employees e2 WHERE e2.department_id = e.department_id
);
Enter fullscreen mode Exit fullscreen mode
Same shape, one crucial difference: the WHERE e2.department_id = e.department_id line. That one line is the entire difference between "global" and "per-row-scoped."
Summary
If the subquery can run on its own and return a sensible result, it's non-correlated. If it needs a value from the outer row to even make sense, it's correlated.
4. SQL's Logical Execution Order (and Where Subqueries Fit)
This trips up a lot of people, so let's nail it down. A SELECT statement is written top to bottom (SELECT → FROM → WHERE → GROUP BY → ...), but it does not execute in that order. The logical order MySQL (and most SQL engines) actually evaluates is:
① FROM (and JOINs) — build the working row set
↓
② WHERE — filter individual rows
↓
③ GROUP BY — bucket rows into groups
↓
④ HAVING — filter groups
↓
⑤ SELECT — compute the output columns
(this is where subqueries in the SELECT list run)
↓
⑥ ORDER BY — sort the final result
↓
⑦ LIMIT — trim the result
Enter fullscreen mode Exit fullscreen mode
Correlated subqueries that live inside the SELECT list (like the ones in my solution below) are evaluated at step ⑤ — for every row/group that survives FROM → WHERE → GROUP BY → HAVING. This is the key insight for our problem:
💡 Key Idea
By the time the correlated subqueries in
SELECTrun, MySQL has already figured out which(student_id, subject)groups exist (thanks toGROUP BY). The subqueries then just get asked, once per group: "for this specific student and subject, what's the first score, and what's the latest score?"
That's it. The whole query is really just: 1) group by student+subject, 2) for each group, ask two side-questions.
Summary
Correlated subqueries in the SELECT list run at step ⑤ — once per group that has already survived filtering and grouping.
5. LeetCode 3421 as Our Running Example: Find Students Who Improved
Table: Scores
| Column Name | Type |
|---|---|
| student_id | int |
| subject | varchar |
| score | int |
| exam_date | varchar |
(student_id, subject, exam_date) is the primary key. Each row is one student's score in one subject on one exam date. score is between 0 and 100.
Task: Find students who have improved. A student counts as improved if, for a given subject, both of these are true:
- They took the exam in that subject on at least two different dates
- Their latest score in that subject is higher than their first score
Return student_id, subject, first_score, latest_score, ordered by student_id, subject ascending.
Example Input:
| student_id | subject | score | exam_date |
|---|---|---|---|
| 101 | Math | 70 | 2023-01-15 |
| 101 | Math | 85 | 2023-02-15 |
| 101 | Physics | 65 | 2023-01-15 |
| 101 | Physics | 60 | 2023-02-15 |
| 102 | Math | 80 | 2023-01-15 |
| 102 | Math | 85 | 2023-02-15 |
| 103 | Math | 90 | 2023-01-15 |
| 104 | Physics | 75 | 2023-01-15 |
| 104 | Physics | 85 | 2023-02-15 |
Expected Output:
| student_id | subject | first_score | latest_score |
|---|---|---|---|
| 101 | Math | 70 | 85 |
| 102 | Math | 80 | 85 |
| 104 | Physics | 75 | 85 |
Notice what got filtered out:
- 101 / Physics — took it twice, but score dropped (65 → 60). Not an improvement.
- 103 / Math — only took the exam once. Doesn't satisfy "at least two different dates."
Simple to state, but notice the core difficulty: for every (student_id, subject) pair, you need to reach back into the same table to find that specific group's minimum date and maximum date, and then the scores tied to those dates. This "reach back into the same table, but scoped to the current row's group" is exactly what a correlated subquery is built for.
6. Before We Write SQL...
Let's forget SQL for a minute.
Imagine you're writing this in C++, Java, or Python.
What would you do? Probably something like this:
for each student
for each subject
earliest_exam
latest_exam
first_score
latest_score
if latest_score > first_score
print()
Enter fullscreen mode Exit fullscreen mode
SQL is simply another way of expressing this algorithm. The GROUP BY gives you the for each student / for each subject part, and the correlated subqueries give you the earliest_exam, latest_exam, first_score, and latest_score lookups. Keep this loop in your head — it makes the query below feel a lot less abstract.
7. My Annotated Solution
Complete Query
WITH t AS (
SELECT
student_id,
subject,
-- Correlated subquery #1: get the score tied to this group's EARLIEST exam_date
(SELECT score
FROM Scores s
WHERE s.student_id = t.student_id
AND s.subject = t.subject
AND s.exam_date = (
-- nested correlated subquery: find the MIN(exam_date) for THIS student+subject
SELECT MIN(exam_date)
FROM Scores ss
WHERE ss.student_id = t.student_id
AND ss.subject = t.subject
)
) AS first_score,
-- Correlated subquery #2: get the score tied to this group's LATEST exam_date
(SELECT score
FROM Scores s
WHERE s.student_id = t.student_id
AND s.subject = t.subject
AND s.exam_date = (
-- nested correlated subquery: find the MAX(exam_date) for THIS student+subject
SELECT MAX(exam_date)
FROM Scores ss
WHERE ss.student_id = t.student_id
AND ss.subject = t.subject
)
) AS latest_score
FROM Scores t
GROUP BY student_id, subject
)
SELECT *
FROM t
WHERE latest_score > first_score
ORDER BY student_id ASC, subject ASC;
Enter fullscreen mode Exit fullscreen mode
What each piece is doing
-
FROM Scores t GROUP BY student_id, subject— collapses the raw exam rows down to one row per(student_id, subject)combo. This is our set of "candidates" to evaluate. -
first_score— a correlated subquery nested inside another correlated subquery. The inner one (MIN(exam_date)scopedWHERE ss.student_id = t.student_id AND ss.subject = t.subject) finds the earliest date scoped to the current groupt. The outer one then fetches thescoreon that exact date, for that exact student and subject. -
latest_score— same idea, but withMAX(exam_date)instead ofMIN(exam_date). -
The condition "at least two different dates" is handled implicitly: if a student/subject only has one exam date, then
first_scoreandlatest_scoreend up being the same value (same date → same score), solatest_score > first_scoreisfalseand that row gets filtered out naturally by the outerWHERE. Nice side effect of the logic — no extraHAVING COUNT(DISTINCT exam_date) >= 2needed. -
Final
WHERE latest_score > first_score— the actual "did they improve" check. -
ORDER BY student_id, subject— matches the problem's required output ordering.
The line that creates the correlation
Everything hinges on this pair of conditions inside the nested subquery:
WHERE
ss.student_id = t.student_id
AND ss.subject = t.subject
Enter fullscreen mode Exit fullscreen mode
These two conditions are the entire magic. They're what tie the inner query's MIN/MAX calculation to this specific row of the outer query t, instead of computing a MIN/MAX across the whole table. Remove them, and the subquery stops being correlated — it just becomes "the earliest date across every student and subject," which isn't what we want at all.
Summary
The nested MIN(exam_date) / MAX(exam_date) subqueries are correlated because they're filtered by the outer group's student_id and subject — that's what scopes them to "this student, this subject" instead of the whole table.
8. Walking the Query Step-by-Step on Real Data
Let's trace it for student 101, subject Math using the example data:
| exam_date | score |
|---|---|
| 2023-01-15 | 70 |
| 2023-02-15 | 85 |
Outer Query
----------------------------------
student_id = 101
subject = 'Math'
----------------------------------
Enter fullscreen mode Exit fullscreen mode
│
▼
Correlated Subquery
----------------------------------
Find MIN(exam_date)
WHERE
student_id = 101
AND subject = 'Math'
----------------------------------
Enter fullscreen mode Exit fullscreen mode
│
▼
2023-01-15
│
▼
Find score where exam_date = 2023-01-15
│
▼
first_score = 70
The latest_score branch follows the exact same path, just swapping MIN(exam_date) for MAX(exam_date), which resolves to 2023-02-15 → score = 85.
Putting it together, step by step:
-
GROUP BYproduces the group(101, Math). - Inner subquery for
first_score:MIN(exam_date)for(101, Math)→2023-01-15. - Outer subquery for
first_score: score whereexam_date = 2023-01-15and student=101, subject=Math →70. - Inner subquery for
latest_score:MAX(exam_date)for(101, Math)→2023-02-15. - Outer subquery for
latest_score: score whereexam_date = 2023-02-15→85. - Row so far:
(101, Math, 70, 85). - Final filter: is
85 > 70? Yes → row survives.
Now trace student 103, subject Math (only one exam):
| exam_date | score |
|---|---|
| 2023-01-15 | 90 |
- Group
(103, Math). -
MIN(exam_date)→2023-01-15→first_score = 90. -
MAX(exam_date)→2023-01-15(same date, only one row) →latest_score = 90. - Final filter: is
90 > 90? No → row is dropped. This is exactly how the "at least two exam dates" rule gets enforced for free.
And student 101, subject Physics (score dropped from 65 to 60):
-
first_score = 65,latest_score = 60. - Is
60 > 65? No → dropped.
That's the entire algorithm, traced by hand.
Summary
Every group's first_score and latest_score are resolved independently by "asking" the same two-step question: find the boundary date, then find the score on that date.
9. Performance Discussion: Subqueries vs Window Functions
MySQL 8.0+ gives us window functions, which can express "first and last value per group" far more efficiently and readably:
Window Function Version
WITH ranked AS (
SELECT
student_id,
subject,
score,
exam_date,
FIRST_VALUE(score) OVER (
PARTITION BY student_id, subject ORDER BY exam_date ASC
) AS first_score,
FIRST_VALUE(score) OVER (
PARTITION BY student_id, subject ORDER BY exam_date DESC
) AS latest_score,
COUNT(*) OVER (PARTITION BY student_id, subject) AS exam_count
FROM Scores
)
SELECT DISTINCT student_id, subject, first_score, latest_score
FROM ranked
WHERE exam_count >= 2 AND latest_score > first_score
ORDER BY student_id, subject;
Enter fullscreen mode Exit fullscreen mode
💡 Key Idea
Window functions typically require the engine to sort/partition the data once, then compute all the values for every partition in that single pass. My correlated-subquery version, by contrast, re-runs
MIN/MAX/lookup subqueries for every group — and in the worst case (or without solid indexing), those inner lookups can degrade toward re-scanning relevant rows per group rather than a single unified pass.
That said — a fair note on my original solution: because of the GROUP BY, the correlated subqueries only run once per distinct (student_id, subject) pair, not once per raw row. With a proper composite index on (student_id, subject, exam_date), MySQL can satisfy each MIN/MAX lookup and the point-lookup for score almost instantly, so in practice this query performs quite well on realistic dataset sizes — it isn't the worst-case O(n²) scenario people sometimes assume correlated subqueries always are. The window-function version is still the more "modern SQL" way to express it, and scales more predictably, but the difference is often smaller than people expect once indexing is in place.
Rule of thumb:
- Reach for correlated subqueries when you need to express "for each row/group, ask a targeted question about a related scope" — they're extremely readable for that.
- Reach for window functions when you're computing this kind of first/last/rank/running-total logic across an entire partitioned dataset — they usually scale better and avoid re-execution per group.
- Always check
EXPLAINon your actual data before assuming either is "the fast one" — data size, indexes, and MySQL version all matter more than the textbook complexity story.
Summary
Correlated subqueries shine for readability and targeted per-group lookups; window functions shine for scale, since they avoid re-running the same calculation over and over.
10. Conclusion & Further Practice
The core idea to take away from this whole post is small enough to fit in one sentence:
A correlated subquery is just a mini-query that gets handed one value from the outer row at a time and answers a question scoped to that value.
Once that clicks, most "hard" correlated-subquery problems stop being hard — they just become "what's the mini-question I need to ask per row/group, and what value from the outer query does it need?"
If you want to build this muscle further, here are a few problems that lean directly on the same skill:
- Second Highest Salary (classic correlated subquery warm-up)
- Department Top Three Salaries
- Rank Scores
- Consecutive Numbers
- Employees Earning More Than Their Managers
Try solving each one twice, the way I did here: once with a correlated subquery (or window function), and once by hand-tracing it like a loop on paper. The loop trace is what actually builds intuition — the SQL syntax is just the encoding of that intuition afterward.
Final Thoughts
Correlated subqueries are often considered one of the trickiest SQL concepts—not because the syntax is difficult, but because it's easy to lose track of which query is executing and which row is being referenced.
Once you realize that the inner query is simply "borrowing" values from the current row of the outer query, the concept becomes much more intuitive.
I hope this article helped make that mental model a little clearer.
Happy learning! 🚀
0 Comments
Log in to join the conversation.No comments yet. Be the first to share your thoughts.