Correlated Subqueries in WHERE Clauses
What you'll learn: How to use correlated subqueries in WHERE clauses to filter rows based on comparisons with related data from the same or other tables.
The Core Idea
A correlated subquery in a WHERE clause filters rows by referencing columns from the outer query. Unlike non-correlated subqueries that run once and return a result set, correlated subqueries execute once for each row in the outer query, using that row's values to perform row-specific calculations or lookups.
Think of it like a personalized check: instead of applying the same filter to everyone, you're asking "Does this specific row meet a condition that depends on its own values?"
How It Works
The outer query processes each row, passing values to the subquery. The subquery then evaluates a condition specific to that row and returns a result that determines whether the row should be included.
Common Pattern:
SELECT employee_id, salary, department_id
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id
);
Here, for each employee (e1), the subquery calculates the average salary for that employee's specific department and checks if their salary exceeds it. Notice e1.department_id from the outer query is referenced inside the subquery—that's the correlation.
When to Use
- Finding rows that exceed department-specific averages
- Identifying records with values above/below related group metrics
- Filtering based on existence of related records with specific conditions
- Comparing individual values against aggregate calculations from related subsets
Key Takeaway: Correlated subqueries in WHERE clauses let you filter rows using dynamic, row-specific comparisons—each row is evaluated against criteria computed specifically for its related data, making them powerful for context-aware filtering.