Correlated vs Non-Correlated Subqueries
Correlated vs Non-Correlated Subqueries
Subqueries can be executed independently or dependently based on whether they reference the outer query scope.
Comparison
| Feature | Non-Correlated Subquery | Correlated Subquery |
|---|---|---|
| Execution Frequency | Runs ONCE for the entire query | Runs ONCE per candidate row of outer query |
| Dependency | Independent of outer query | References outer query columns |
| Performance | Generally fast (cached result) | Can be slow on large tables (O(N2)) |
| Optimization | Direct evaluation | Can often be rewritten as a JOIN |
1. Non-Correlated Subquery (Independent)
The inner subquery does not reference outer query columns. It executes once, producing a constant value or set used by the outer query.
-- Subquery executes ONCE to calculate company-wide average salary
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);2. Correlated Subquery (Dependent)
The inner subquery references columns from the current row of the outer query (correlated alias e1). Conceptually, the subquery executes for each candidate row.
-- Subquery executes for each department to find above-average earners in that department
SELECT e1.name, e1.department, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department = e1.department
);