44
What is a correlated subquery and how does it differ from a normal subquery?
Level: Intermediate to Advanced — A key question that also touches on query performance.
The difference lies in whether the inner query depends on the outer query.
- Normal (non-correlated) subquery: Runs independently, once, before the outer query. Its result is then used by the outer query.
- Correlated subquery: References a column from the outer query, so it must run once for every row the outer query processes.
How to spot one: If the inner query uses a table alias/column from the outer query, it's correlated. It cannot run on its own — it "depends" on the outer row currently being evaluated.
Side-by-Side Comparison
| Feature |
Non-Correlated |
Correlated |
| Depends on outer query? |
No |
Yes |
| Execution |
Once |
Once per outer row |
| Can run standalone? |
Yes |
No |
| Performance |
Generally faster |
Can be slower (row-by-row) |
| Common with |
IN, =, comparison |
EXISTS, comparison |
How Each Executes
Non-Correlated
- Inner query runs first, one time
- Result passed up to outer query
- Efficient for large data
Correlated
- Outer query picks a row
- Inner query runs for THAT row
- Repeats for every outer row
Quick Example
-- NON-CORRELATED: inner query runs once (average is fixed)
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- CORRELATED: inner query references outer alias 'e1'
-- Finds employees earning more than their OWN department's average
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 -- depends on outer row
);
-- CORRELATED with EXISTS: departments that have employees
SELECT d.dept_name
FROM departments d
WHERE EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);
Performance tip: Correlated subqueries can be slow on large tables because they run repeatedly. Where possible, rewrite them as JOINs, which the optimizer usually handles more efficiently.
Interviewer tip: The one-liner they want — "A normal subquery runs once independently, while a correlated subquery references the outer query and runs once per outer row — making it powerful but potentially slower."