Course contentsShow
← Data Science
Lesson 980 of 2,14525. SQL Subqueries and CTEsFree lesson

Converting Correlated to Non-Correlated Subqueries

Learn techniques to rewrite correlated subqueries as joins or window functions for better performance.

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.