In this lesson, you will learn how to create "context-aware" filters by letting a subquery look back at the row it is currently processing in the outer query.
The Concept: Looking Back
Up until now, you have likely used subqueries that run once, independently, and hand a result back to the main query. A correlated subquery is different. It acts like a "helper" that runs once for every single row in your outer table.
Because it runs for each row, it can "see" the values in the current row of the outer table and use them to make a specific decision. Think of it like a teacher who looks at each student’s grade individually to decide if they pass, rather than just using one global average for the whole class.
Putting it into Action
Imagine you have a list of employees and want to find everyone who earns more than the average salary for their specific department. A standard subquery can't do this because it doesn't know which department the current employee belongs to. A correlated subquery does:
SELECT employee_name, salary, department_id
FROM employees AS outer_emp
WHERE salary > (
SELECT AVG(salary)
FROM employees AS inner_emp
WHERE inner_emp.department_id = outer_emp.department_id
);
In this example, for every row in outer_emp, the subquery calculates the AVG(salary) specifically for that row’s department_id. If the employee’s salary is higher than that calculated average, the WHERE clause keeps the row.
Why Use This?
Correlated subqueries allow you to perform filtering based on dynamic conditions that change depending on which row you are currently looking at. It turns your SQL from a static "filter everything by one rule" approach into a precise tool that adapts to the data context of every individual record.
Key Takeaway: A correlated subquery uses values from the outer query to customize its logic for every row it evaluates, allowing for granular, context-aware data filtering.