Converting Correlated to Non-Correlated Subqueries
What you'll learn: How to rewrite slow correlated subqueries as faster joins or non-correlated alternatives for dramatically improved performance.
Why Convert Correlated Subqueries?
Remember from lesson 979 that correlated subqueries execute once per outer row—like checking your phone for each step you take on a long walk. By converting them to non-correlated subqueries or joins, you transform that repetitive process into a single, efficient operation.
The Core Technique: Think "Precompute Once, Then Match"
The key insight is to calculate what you need separately first, then join the results. Instead of asking "what's this person's average?" for each row, compute everyone's averages once and merge them back.
Pattern 1: Convert to JOIN
Correlated approach:
SELECT employee_name, salary
FROM employees e1
WHERE salary > (SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id);
Non-correlated with JOIN:
SELECT e.employee_name, e.salary
FROM employees e
INNER JOIN (
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
) dept_avgs ON e.department_id = dept_avgs.department_id
WHERE e.salary > dept_avgs.avg_sal;
The Mental Model
Think of it like preparing for a party: instead of going to the store separately for each guest's dietary needs (correlated), make one list of all requirements and shop once (non-correlated).
When It Works Best
This conversion shines when:
- The subquery aggregates data (SUM, AVG, COUNT)
- You're comparing row values to group-level statistics
- The same calculation would repeat many times
Key Takeaway: Converting correlated subqueries to joins or derived tables eliminates row-by-row execution, often reducing query time from minutes to seconds by computing aggregates once and matching efficiently.