What is the difference between a correlated and a non-correlated subquery?
A non-correlated subquery runs once and does not refer to the outer query. A correlated subquery refers to a column of the outer row, so it is evaluated once for each outer row, at least logically.
Explain it simply
Non-correlated is like asking, "What is the class topper's mark?" once and then comparing everyone to that number. Correlated is like asking, for each student, "What is the average mark in this student's own section?" The inner question changes with every outer row.
Example
Non-correlated, runs once:
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);Correlated, depends on the outer row e:
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
WHERE dept_id = e.dept_id
);The second query returns employees who earn more than the average of their own department. The inner query cannot run alone because e.dept_id is undefined without the outer query.
Optimizers often rewrite correlated subqueries into joins, so they are not always slow, but a poorly indexed correlated subquery on a big table can be.
What to say in the interview
"A correlated subquery references the outer query and is conceptually re-run per outer row. A non-correlated one runs once. For large data I check the plan or rewrite correlated logic as a join or window function."
Common mistakes
- Assuming correlated subqueries are always slow, or always fine.