Correlated subqueries in SQL are nested queries where the
inner query references a column from the outer query. These subqueries are
evaluated for each row processed by the outer query.
Consider a table named Employees
with columns EmployeeID, EmployeeName, and Salary. We want to retrieve
all employees whose salary is greater than the average salary of their
department.
SELECT EmployeeID,
EmployeeName, Salary FROM Employees e1 WHERE Salary > ( SELECT AVG(Salary) FROM Employees e2 WHERE
e1.DepartmentID = e2.DepartmentID );
In this example, the subquery (SELECT AVG(Salary) FROM Employees e2 WHERE e1.DepartmentID =
e2.DepartmentID) is correlated because it references the DepartmentID column from the
outer query (e1). For each row
processed by the outer query, the subquery calculates the average salary of the
corresponding department. The outer query then retrieves the EmployeeID, EmployeeName, and Salary where the salary is
greater than the average salary of the department. This query returns employees
whose salary exceeds the average salary of their department.
Comments
Post a Comment