What is a Correlated Subquery?

Introduction

A correlated subquery in SQL Server is a subquery that uses references from either one or more columns in the outer query. As a result of this relationship, a dependency is created where the subquery has to keep being evaluated every time each time a row is processed by the outer query.

What distinguishes a correlated subquery is the following:

They are executed once for every row that gets selected by the outer query.
Columns from the outer query’s table is referenced by the correlated subquery.
The inner query depends on the outer query for its values.

Example

Suppose we have the following query:

SELECT e.FirstName, e.LastName, e.Salary
FROM Employees e
WHERE e.Salary > (
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID = e.DepartmentID
)

The subquery calculates the average salary for every department. The correlation occurs through e.DepartmentID in the subquery where it references the outer query’s table alias, Employees e. The outer query returns the salary of the employee which is higher than the average of the department.

Conclusion

Although correlated queries are powerful, they can have a negative impact when it comes to performance due to running many times. When dealing with larger datasets, using window functions or joins are alternatives that are more efficient.