What is a Correlated Subquery?
You'll learn how correlated subqueries create a special relationship with their outer query, executing repeatedly for each row instead of just once.
The Core Idea
Unlike non-correlated subqueries that run independently and return results before the outer query uses them, a correlated subquery references columns from the outer query. This creates a dependency: the subquery must execute once for each row processed by the outer query, using values from that specific row.
Think of it like a nested loop in programming. For every row the outer query examines, the inner subquery runs with fresh data from that outer row, producing a customized result.
How It Works
In a correlated subquery, you'll see the outer table's alias referenced inside the subquery. This tells the database: "Use the current row's values from the outer query when evaluating this subquery."
SELECT employee_name, salary
FROM employees e1
WHERE salary > (SELECT AVG(salary)
FROM employees e2
WHERE e2.department = e1.department);
Here, e1.department connects the inner and outer queries. For each employee in e1, the subquery calculates the average salary for that employee's specific department, then compares their salary to that average.
The Performance Trade-off
Because correlated subqueries execute once per outer row, they can be slower than non-correlated alternatives. If the outer query returns 1,000 rows, the subquery runs 1,000 times. However, they're often more intuitive for row-by-row comparisons.
Key Takeaway: A correlated subquery references columns from the outer query and executes once for each outer row, creating a dynamic, row-specific evaluation rather than a single pre-computed result.