Correlated Subquery in SQL Server

What is a correlated subquery?

A correlated subquery is an inner query that refers to a column from the outer query.

It is connected to the current row being processed by the outer query. Conceptually, the subquery is evaluated for each row of the outer query.

Example tables

Employees

Example: Employees earning above their department average

SELECT
    E.EmployeeName,
    E.DepartmentId,
    E.Salary
FROM Employees E
WHERE E.Salary >
(
    SELECT AVG(E2.Salary)
    FROM Employees E2
    WHERE E2.DepartmentId = E.DepartmentId
);

How it works

For each employee:

1. The outer query selects an employee as E.

2. The subquery calculates the average salary for that employee’s department.

3. The employee’s salary is compared with the department average.

4. The employee is returned when the salary is higher.

The correlation is:

E2.DepartmentId = E.DepartmentId

• E2.DepartmentId belongs to the subquery.

• E.DepartmentId belongs to the outer query.

Result

Department averages:

• Department 10: (60,000 + 40,000) / 2 = 50,000

• Department 20: (70,000 + 50,000) / 2 = 60,000

Normal vs correlated subquery

Normal subquery

The inner query runs independently:

SELECT EmployeeName, Salary
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

It compares each employee against the overall company average.

Correlated subquery

The inner query depends on the outer row:

SELECT EmployeeName, Salary
FROM Employees E
WHERE Salary >
(
    SELECT AVG(E2.Salary)
    FROM Employees E2
    WHERE E2.DepartmentId = E.DepartmentId
);

It compares each employee against their own department average.

Correlated subquery with EXISTS

Find customers who have at least one order:

SELECT
    C.CustomerId,
    C.CustomerName
FROM Customers C
WHERE EXISTS
(
    SELECT 1
    FROM Orders O
    WHERE O.CustomerId = C.CustomerId
);

The correlation is:

O.CustomerId = C.CustomerId

EXISTS returns true as soon as SQL Server finds a matching order.

Correlated subquery with NOT EXISTS

Find customers who have never placed an order:

SELECT
    C.CustomerId,
    C.CustomerName
FROM Customers C
WHERE NOT EXISTS
(
    SELECT 1
    FROM Orders O
    WHERE O.CustomerId = C.CustomerId
);

Usage

Correlated subqueries are commonly used for:

• Comparing a row with its group average

• Checking whether related records exist

• Finding rows without related records

• Finding maximum or minimum values within each group

• Applying row-specific filtering conditions

Key points

• It references a column from the outer query.

• It cannot normally run independently because it needs the outer row.

• Table aliases are important for distinguishing inner and outer references.

• It is commonly used with EXISTS and NOT EXISTS.

• Conceptually, it runs once for every outer row.

• SQL Server may optimize it internally into a join or another efficient operation.

• Performance can become slow on large tables if the correlation columns are not indexed.

• Always check the execution plan for performance-sensitive queries.

Interview answer:
“A correlated subquery is a subquery that references columns from its outer query. It produces or checks a result based on the current outer row and is commonly used for group-based comparisons and EXISTS or NOT EXISTS checks.”