Click or drag to resize

SA0128 : Avoid using correlated subqueries. Consider using JOIN instead

The topic describes the SA0128 analysis rule.

Message

Avoid using correlated subqueries. Consider using JOIN instead

Description

The rule checks for usage of correlated subqueries.

Unless the query optimizer re-writes the correlated subquery with a join, the correlated subquery has to use a nested loop join, which means that the subquery will be executed repeatedly, once for each row that might be selected by the outer query.

For this type of query, you can consider using a left join, which is more likely to use a hash/merge join operator and this way increase the query performance and consistency.

Example:

SQL
1SELECT DISTINCT pv1.ProductID, pv1.BusinessEntityID
2FROM Purchasing.ProductVendor pv1
3WHERE ProductID IN
4    (SELECT pv2.ProductID
5     FROM Purchasing.ProductVendor pv2
6     WHERE pv1.BusinessEntityID != pv2.BusinessEntityID)
7ORDER  BY pv1.BusinessEntityID

The preceding nested query is equivalent to this self-join:

SQL
1USE AdventureWorks2008R2;
2GO
3SELECT DISTINCT pv1.ProductID, pv1.BusinessEntityID
4FROM Purchasing.ProductVendor pv1
5INNER JOIN Purchasing.ProductVendor pv2
6ON pv1.ProductID = pv2.ProductID
7    AND pv1.BusinessEntityID != pv2.BusinessEntityID
8ORDER BY pv1.BusinessEntityID

Scope

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

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0128

IgnoreCorrelatedQueriesInsideExistsClause

Ingore correlated queries inside EXISTS clause.

yes

Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Categories

Performance Rules

Additional Information
Example Test Script
SQL
 1SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID 
 2FROM Person.Person AS c JOIN HumanResources.Employee AS e
 3ON e.BusinessEntityID = c.BusinessEntityID 
 4WHERE 5000.00 IN
 5    (SELECT Bonus
 6    FROM Sales.SalesPerson sp
 7    WHERE e.BusinessEntityID = sp.BusinessEntityID) ;
 8
 9SELECT 
10        F.custCode, 
11        F.CustLocNum, 
12        (SELECT COUNT(*) FROM DimFacilitySupplier S WHERE S.FacilityKey = F.FacilityKey) AS Cnt
13FROM #DimFacility F;
14
15SELECT 
16        F.custCode, 
17        F.CustLocNum
18FROM DimFacility F
19WHERE F.FacilityKey = ANY (SELECT S.FacilityKey FROM DimFacilitySupplier S);
20
21SELECT 1
22FROM dbo.Table_1 t1
23WHERE N'Hi' IN
24  (SELECT t2.testdata1
25    FROM dbo.Table_2 t2
26    WHERE t1.testkey = t2.testkey);

Analysis Results

 MessageLineColumn
1SA0128 : Avoid using correlated subqueries. Consider using JOIN instead.55
2SA0128 : Avoid using correlated subqueries. Consider using JOIN instead.122
3SA0128 : Avoid using correlated subqueries. Consider using JOIN instead.243
See Also

Other Resources