Home

Description

The SELECT statements used as argument of EXISTS statements should be selective rule states that when using SELECT statements as part of an EXISTS statement, the SELECT statement should be designed to return a small number of results. This means that the SELECT statement should include a WHERE clause with conditions that will limit the number of rows that are returned from the query. This will help ensure that the EXISTS statement runs quickly and efficiently.

Key Benefits

• Faster Execution: This ensures that the EXISTS statement can quickly determine if the specified condition is true or false, without having to evaluate the entire result set. :
• Optimized Performance: By using SELECT statements that are selective, the EXISTS statement can quickly determine if the specified condition is true or false. This can help to improve the performance of the query. :
• Reduced Complexity: By using SELECT statements that are selective, the query can be simplified and the complexity of the query can be reduced. :

Non-compliant Code Example

SELECT TypeId, TypeName, TypeCategory
FROM Type1
WHERE EXISTS(SELECT TypeId FROM Type2)  --Non compliant code (SELECT statements used as argument of EXISTS statements is not selective)
GO

Compliant Code Example

SELECT TypeId, TypeName, TypeCategory
FROM Type1
WHERE EXISTS(SELECT TypeId FROM Type2 WHERE TYPE2.Id = Type1.Id)  --Compliant code (SELECT statements used as argument of EXISTS statements is selective)
GO