Correlated Subqueries in SQL Server

Correlated Subqueries in SQL

A correlated subquery is a subquery that references columns from the outer query. A regular subquery is executed once, and its results are used by the outer query. In contrast, in case of correlated subquery, inner subquery is executed once for each row processed by the outer query. This makes them a useful tool for row-by-row logic, where you need to compare a value in one table with a related value in another.

To know more about the difference between Subquery, Nested Subquery and Correlated Subquery, please refer to the article – Difference between Subquery, Nested Subquery and Correlated Subquery

Key Characteristics

1. Dependency:
   • The subquery depends on the outer query for its values.
2. Execution:
   • The subquery is re-evaluated for every row processed by the outer query.
3. Performance:
   • Can be less efficient than joins or regular subqueries due to repeated execution.

How It Works

Unlike a standard, or non-correlated, subquery that executes once and returns its result to the outer query, a correlated subquery’s execution is dependent on the outer query’s progress.

Imagine the outer query is a loop that processes each row one by one. For every single row it processes, the correlated subquery is run to retrieve or calculate a value that is then used to filter or evaluate that specific row. This makes them more resource-intensive than other query types on large datasets, but they are often the most straightforward way to express complex conditional logic.

Example

You have two tables:

1. Employees: Contains employee details.
2. Departments: Contains department details.

You want to find employees whose salary is greater than the average salary of their respective departments.

Table Structures

Employees:

EmployeeID	Name	Salary	DepartmentID
1	Alice	90000	1
2	Bob	75000	1
3	Charlie	80000	2
4	David	72000	2

Departments:

DepartmentID	DepartmentName
1	HR
2	IT

Correlated Subquery

SELECT e.Name, e.Salary, d.DepartmentName
FROM Employees e
JOIN Departments d
ON e.DepartmentID = d.DepartmentID
WHERE e.Salary > (
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID = e.DepartmentID
);

How It Works

1. For each row in the Employees table:
   • The subquery calculates the average salary for the department (DepartmentID = e.DepartmentID).
2. The outer query compares the employee’s salary with this average.
3. Only employees meeting the condition are returned.

Result

Name	Salary	DepartmentName
Alice	90000	HR

Use Cases of Correlated Subqueries

1. Row-specific Aggregations:
   • Compare a row’s value with an aggregate computed over a subset of rows.
2. Filtering Data:
   • Identify rows based on conditions involving other rows.
3. Complex Relationships:
   • Handle relationships not easily modeled using joins.

Performance Considerations

• Correlated subqueries can be slower than joins or non-correlated subqueries because they execute for each row of the outer query.
• Optimization Tips:
  • Rewrite using joins when possible.
  • Use indexes to improve the performance of the subquery.

When to Use a Correlated Subquery

Correlated subqueries are particularly useful for scenarios where you need to:

• Compare a row’s value to an aggregate value for a specific group. (e.g., finding all employees with a salary above their department’s average).
• Check for the existence of related rows in another table. (e.g., finding all customers who have placed an order in the last 30 days).
• Identify records that are not present in a related table. (e.g., finding all departments that currently have no employees).

Summary

Correlated subqueries are a powerful SQL feature for handling row-specific comparisons and complex conditions. While they can simplify logic for certain queries, careful use is required to manage performance, especially on large datasets.