Anatomy of a Correlated Subquery
How the inner query references the outer row and the per-row execution model.
Anatomy of a Correlated Subquery is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Makes a Subquery Correlated
Interviewers split subqueries into two camps. A plain (uncorrelated) subquery can run on its own. A correlated subquery references a column from the outer query, so it cannot run standalone.
- Uncorrelated: evaluated once, result reused for every outer row.
- Correlated: re-evaluated once per outer row, because it depends on that row.
The tell-tale sign is a column from the outer table appearing inside the inner query. Spot that and you can name the pattern instantly.
The Per-Row Execution Model
Picture the engine looping over outer rows. For each outer row it plugs that row's values into the inner query, runs it, and uses the result to decide or compute something.
This is the mental model interviewers want you to verbalize: "the inner query runs once for every outer row."
That phrasing also hints at the classic follow-up: correlated subqueries can be slow because the inner query may execute thousands of times. We will fix that in lesson 4.
Spotting the Outer Reference
Here employees and a salaried outer alias e1 drive an inner query that reads e1.dept_id. That reference into the outer row is the correlation.
Remove the alias prefix and the inner query no longer compiles on its own. That dependency is exactly what makes it correlated.
SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);Reading That Query Out Loud
Translate the previous query into plain English the way you would in an interview:
"For each employee e1, find the average salary of their own department, and keep the employee only if they earn more than that department average."
The inner query's WHERE e2.dept_id = e1.dept_id ties the average to this employee's department. Without that line you would compare everyone to the company-wide average instead.
Aliases Are Mandatory
When the inner and outer query touch the same table, you must alias both so the engine knows which row a column belongs to.
e1= the outer row being tested.e2= the inner scan over the table.
Drop the aliases and dept_id becomes ambiguous; many engines will then silently bind it to the inner table, breaking the correlation. Interviewers plant this exact mistake.
Correlated Subquery in SELECT
Correlated subqueries are not limited to WHERE. In a SELECT list they produce a computed column, again evaluated per outer row.
Below, each order shows how many other orders the same customer placed. The inner count is correlated through o.customer_id.
SELECT o.order_id,
o.customer_id,
(SELECT COUNT(*)
FROM orders o2
WHERE o2.customer_id = o.customer_id) AS customer_order_count
FROM orders o;Scalar Means Exactly One Value
A correlated subquery used in SELECT or compared with =, >, < must return a single scalar value per outer row.
If it returns more than one row, the database raises an error such as "subquery returns more than one row."
Aggregates like COUNT, MAX, or AVG guarantee one value, which is why they are common inside scalar correlated subqueries. Knowing this rule prevents a frequent runtime surprise.
When the Subquery Returns NULL
A scalar correlated subquery can match zero inner rows. An aggregate then returns NULL (or, for COUNT, returns 0).
That NULL flows into your outer expression. Comparisons against NULL yield UNKNOWN, so the outer row may be silently excluded.
If you need a fallback, wrap the subquery in COALESCE. Interviewers like to ask what happens when no inner row matches, expecting you to mention NULL behavior.
SELECT c.customer_id,
COALESCE((SELECT MAX(o.amount)
FROM orders o
WHERE o.customer_id = c.customer_id), 0) AS biggest_order
FROM customers c;Worked Example: Latest Order Date
A common task: show each customer with their most recent order date. A correlated subquery in SELECT does it directly.
For every customer row, the inner query finds the MAX order date for that customer via o.customer_id = c.customer_id.
SELECT c.customer_id,
c.name,
(SELECT MAX(o.order_date)
FROM orders o
WHERE o.customer_id = c.customer_id) AS last_order_date
FROM customers c;Why It Can Be Slow
Because the inner query runs once per outer row, a correlated subquery over a large outer table can fire millions of inner executions.
- An index on the correlated column (here
orders.customer_id) lets each inner run finish quickly. - Without an index, each run may scan the whole table, giving roughly O(n*m) work.
In interviews, always mention the index and the join-rewrite as your performance levers.
Correlated vs Uncorrelated Side by Side
The difference is one line. The uncorrelated version compares everyone to the company average; the correlated version compares each person to their own department.
Read both and notice how the single WHERE e2.dept_id = e1.dept_id line changes the entire meaning.
-- Uncorrelated: one global average, computed once
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Correlated: per-department average, recomputed per row
SELECT e1.name FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary) FROM employees e2
WHERE e2.dept_id = e1.dept_id
);Quick Check
Test your grasp of what defines a correlated subquery.
Recap: Anatomy of a Correlated Subquery
Key takeaways:
- A correlated subquery references the outer row and runs once per outer row.
- Alias both tables when they are the same table to keep the correlation unambiguous.
- Scalar usage must return exactly one value; zero matches yield NULL, so guard with
COALESCE. - It can live in SELECT or WHERE, and performance hinges on indexing the correlated column.
Say "runs once per outer row" in the interview and you have nailed the core concept.
Frequently asked questions
Is the “Anatomy of a Correlated Subquery” lesson free?
Yes — the full text of “Anatomy of a Correlated Subquery” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “Anatomy of a Correlated Subquery”?
How the inner query references the outer row and the per-row execution model. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Anatomy of a Correlated Subquery” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Anatomy of a Correlated Subquery
- Per-Group Aggregates Without GROUP BY
- Correlated EXISTS and NOT EXISTS
- Rewriting Correlated Subqueries as Joins