SA0128 : Avoid using correlated subqueries. Consider using JOIN instead

Introduction

Correlated subqueries can cause performance issues, as they are executed for every row in the outer query.

Description

A correlated subquery can lead to performance problems in SQL Server. This issue arises because a correlated subquery is executed repeatedly for every row in the outer query. This can significantly slow down query performance, especially with large datasets, unless the optimizer transforms it into a more efficient join.

For example:

-- Example of a correlated subquery
SELECT e.EmployeeID, e.Name
FROM Employees e
WHERE e.Salary > (SELECT AVG(Salary) FROM Salaries s WHERE s.DepartmentID = e.DepartmentID);

In this example, the SELECT AVG(Salary) subquery is correlated with the outer query by e.DepartmentID . The subquery executes for each employee, leading to potential performance degradation due to repetitive calculations.

Increased execution time due to repeated subquery evaluations for each outer row.
Higher resource consumption, affecting database server performance.

How to fix

Consider using a self-join instead of a correlated subquery to improve query performance and consistency.

Follow these steps to address the issue:

1.Identify the subquery in your SQL query that is causing performance issues due to its correlation with the outer query.

2.Convert the correlated subquery into an equivalent self-join to allow SQL Server to utilize more efficient join operations such as hash or merge joins.

3.Ensure the join conditions correctly replicate the logic of the original subquery for accurate results.

For example:

-- Original correlated subquery
SELECT e.EmployeeID, e.Name
FROM Employees e
WHERE e.Salary > (SELECT AVG(Salary) FROM Salaries s WHERE s.DepartmentID = e.DepartmentID);

Can be rewritten using a self-join as follows:

-- Rewritten query using a self-join
SELECT DISTINCT e.EmployeeID, e.Name
FROM Employees e
JOIN (
    SELECT DepartmentID, AVG(Salary) AS AvgSalary
    FROM Salaries
    GROUP BY DepartmentID
) avgSalaries ON e.DepartmentID = avgSalaries.DepartmentID
WHERE e.Salary > avgSalaries.AvgSalary;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Name	Description	Default Value
IgnoreCorrelatedQueriesInsideExistsClause	Ignore correlated queries inside EXISTS clause.	yes

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

1 hour per issue.

Additional Information

Correlated Subqueries

Example Test SQL

SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000.00 IN
    (SELECT Bonus
    FROM Sales.SalesPerson sp
    WHERE e.BusinessEntityID = sp.BusinessEntityID) ;

SELECT
  F.custCode,
  F.CustLocNum,
  (SELECT COUNT(*) FROM DimFacilitySupplier S WHERE S.FacilityKey = F.FacilityKey) AS Cnt
FROM #DimFacility F;

SELECT
  F.custCode,
  F.CustLocNum
FROM DimFacility F
WHERE F.FacilityKey = ANY (SELECT S.FacilityKey FROM DimFacilitySupplier S);

SELECT 1
FROM dbo.Table_1 t1
WHERE N'Hi' IN
  (SELECT t2.testdata1
    FROM dbo.Table_2 t2
    WHERE t1.testkey = t2.testkey);

Analysis Results

	Message	Line	Column
1	SA0128 : Avoid using correlated subqueries. Consider using JOIN instead.	5	5
2	SA0128 : Avoid using correlated subqueries. Consider using JOIN instead.	12	2
3	SA0128 : Avoid using correlated subqueries. Consider using JOIN instead.	24	3

Analysis Rules