A correlated subquery is a type of subquery that depends on the outer query for its values. Unlike a regular subquery, which runs once and provides a static result, a correlated subquery executes repeatedly—once for each row processed by the outer query.
In simple terms, the correlated subquery refers to data from the outer query for each row, making it dynamic and specific to that row. This means the inner query needs to be re-executed for each row in the outer query.
Example (Simple Explanation):
Imagine you want to find employees who have a higher salary than the average salary in their own department.
SELECT e1.employee_id, e1.name
FROM employees e1
WHERE e1.salary > (SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e1.department_id);
Here, the correlated subquery calculates the average salary for each employee’s department.
For each employee (processed by the outer query), the inner query calculates the average salary for that employee’s department.
The subquery depends on e1.department_id from the outer query, so it runs once for each employee.
In short, a correlated subquery relies on the outer query’s data for each row, making it "correlated" with the outer query.
Top comments (0)