What is a Correlated Subquery in SQL?
Master SQL correlated subqueries: how they reference the outer row, run per row, pair with EXISTS, and when to rewrite them as JOINs or window functions.
Expected Interview Answer
A correlated subquery is a nested query that references columns from the outer query, so it is re-evaluated once for every row the outer query processes.
Unlike a non-correlated subquery that runs a single time independently, a correlated subquery depends on the current outer row and cannot be executed on its own. It is commonly paired with EXISTS, NOT EXISTS, or comparison operators to test each outer row against related inner data. Because it runs per row, it can be slower on large datasets, and it is often rewritable as a JOIN or window function for better performance.
- Expresses per-row comparisons against related data naturally
- Works cleanly with EXISTS and NOT EXISTS checks
- Handles row-by-row logic like 'above the group's average'
- Can reference the outer row to filter contextually
- Often more readable than complex self-joins for existence tests
AI Mentor Explanation
Imagine checking, for each batter, whether they outscored their own team's average in that specific match. You cannot compute one figure for everyone; for every batter you look up their particular match and recompute that match's average. Repeating the inner calculation freshly for each batter, using that batter's match as context, is exactly how a correlated subquery re-runs per outer row.
Step-by-Step Explanation
Step 1
Spot the per-row dependency
Recognise that the inner query needs a value from the current outer row, such as its department or account.
Step 2
Reference the outer column
Inside the subquery, refer to the outer table's column (e.g. o.dept_id = i.dept_id) to correlate them.
Step 3
Choose EXISTS or a comparison
Use EXISTS/NOT EXISTS for existence tests, or a comparison operator against a scalar per-row result.
Step 4
Understand per-row execution
Accept that the subquery conceptually re-runs for each outer row, which drives correctness and cost.
Step 5
Consider a rewrite
If performance suffers, rewrite as a JOIN or window function that computes the same result set-wise.
What Interviewer Expects
- Correlated subquery references the outer query and runs per outer row
- Contrast with a non-correlated subquery that runs once independently
- Correct use of EXISTS and NOT EXISTS
- Awareness of performance cost on large tables
- Ability to rewrite it as a JOIN or window function
Common Mistakes
- Thinking a correlated subquery runs only once like an independent one
- Forgetting the outer-column reference, breaking the correlation
- Using it where a set-based JOIN would be far more efficient
- Confusing EXISTS (existence) with IN (value matching) semantics
- Ignoring NULL pitfalls when using NOT IN instead of NOT EXISTS
Best Answer (HR Friendly)
“A correlated subquery is an inner query that depends on the outer query, so it re-runs for every row rather than just once. It is useful for row-by-row checks, like finding employees who earn more than their own department's average, though it can be slower on big tables.”
Code Example
-- Employees earning more than their OWN department's average
SELECT e.name, e.salary, e.dept_id
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e.dept_id -- correlation to the outer row
);
-- Customers who have placed at least one order, using EXISTS
SELECT c.name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);Follow-up Questions
- How does a correlated subquery differ from a non-correlated one?
- Why is EXISTS often preferred over IN for correlated checks?
- How can you rewrite a correlated subquery as a JOIN?
- What are the performance implications of correlated subqueries?
- How do window functions replace some correlated subqueries?
MCQ Practice
1. How often is a correlated subquery conceptually evaluated?
A correlated subquery references the outer row, so it is conceptually re-evaluated for each row the outer query processes.
2. What distinguishes a correlated subquery from a normal one?
The defining trait is that it references the outer query's columns, making it dependent and re-run per outer row.
3. Which operator pairs most naturally with a correlated subquery for existence tests?
EXISTS (and NOT EXISTS) test whether the correlated subquery returns any row, which is a common existence-check pattern.
Flash Cards
What is a correlated subquery? — A subquery that references the outer query's columns and is re-evaluated once per outer row.
Correlated vs non-correlated? — Correlated depends on the outer row and runs per row; non-correlated runs once, independently.
Why use EXISTS with a correlated subquery? — EXISTS efficiently tests whether related rows exist without returning their values, and handles NULLs safely.
How to improve a slow correlated subquery? — Rewrite it as a JOIN or a window function that computes the result set-based instead of per row.