In a standard (independent) subquery, the inner query executes once, produces an intermediate result, and passes that value to the outer query. In contrast, a Correlated Subquery depends directly on values from the current row being evaluated by the outer query.
Because the inner query references outer columns, it cannot be pre-computed. The database engine must evaluate the inner query repeatedly for every single row processed by the outer query!
1. Anatomy of a Correlated Subquery
Notice the outer table reference (e.department_id) inside the inner query:
1-- Find employees who earn MORE than the average salary of THEIR OWN department:
Because a correlated subquery executes row-by-row:
If the outer query processes 100,000 rows...
The inner query will execute 100,000 individual times!
If the inner query table is not properly indexed, the database will grind to a halt.
4. Rewriting Correlated Subqueries as JOINs
Experienced database engineers frequently rewrite correlated subqueries as INNER JOINs on pre-aggregated derived tables to achieve massive speedups:
1-- REWRITTEN AS A JOIN (Runs up to 500x faster!):
2SELECT
3 e.employee_id,
4 e.full_name,
5 e.salary,
6 e.department_id
7FROM employees e
8INNERJOIN(
9-- Pre-aggregate department averages ONCE:
10SELECT department_id,AVG(salary)AS dept_avg
11FROM employees
12GROUPBY department_id
13) d_avg ON e.department_id = d_avg.department_id
14WHERE e.salary > d_avg.dept_avg;
Why the JOIN is Faster:
The derived table calculates all departmental averages in a single pass; the outer query then performs a hash or index join, eliminating repeated subquery scans!
5. Best Practices & Common Pitfalls
Always Index the Correlating Column: In WHERE sub.department_id = e.department_id, the column sub.department_idmust be indexed. If it is unindexed, every row in the outer table triggers a full table scan in the inner table ($N imes M$ operations).
Consider Window Functions: In modern MySQL 8.0+, many correlated subquery patterns (like ranking and department comparisons) are much more efficiently solved using Window Functions (AVG(salary) OVER (PARTITION BY dept_id)).
Interactive Quiz• Knowledge Check
Multiple Choice Questions
Question 1
What defines a Correlated Subquery in SQL?
Question 2
How many times does the inner query of a correlated subquery execute relative to the outer query?
Question 3
Which optimization technique can replace a slow correlated subquery with a single-pass aggregation?
Question 4
Which index is most critical for ensuring acceptable performance of a correlated subquery?
Question 5
What modern SQL feature introduced in MySQL 8.0 frequently eliminates the need for correlated subqueries for departmental average comparisons?
Next Lesson
Semi-Joins & Anti-Joins with EXISTS & NOT EXISTS
Continue learning with hands-on practice, examples, and exercises in the upcoming topic.