Home

Description

This rule states that subqueries that produce NULL values should not be used in NOT IN conditions. This is because when a subquery produces a NULL value, it is not considered an element of the set that is being compared, and thus the comparison will always return false. This can lead to unexpected results, and should be avoided. To ensure that the comparison works as expected, use a NOT EXISTS condition instead.

Key Benefits

• No NULL values: Nullable subqueries should not be used in NOT IN conditions, as this will result in no NULL values being returned.
• Efficient query execution: By avoiding the use of nullable subqueries, the query execution time is reduced, as the query engine does not have to process any NULL values.
• Accurate results: Without the use of nullable subqueries, the results of the query will be more accurate, as any NULL values will not be included in the results.

Non-compliant Code Example

CREATE TABLE Type1
(
    TypeId INT,
    TypeName VARCHAR(50),
    TypeCategory VARCHAR(50)
)
GO
CREATE TABLE Type2
( 
    TypeId INT,
    TypeName VARCHAR(50),
    TypeCategory VARCHAR(50)
)
GO

SELECT TypeId, TypeName, TypeCategory
FROM Type1  
WHERE TypeId NOT IN (SELECT TypeId FROM Type2)  --Non compliant code (TypeId column might be null)

Compliant Code Example

CREATE TABLE Type1
(
    TypeId INT,
    TypeName VARCHAR(50),
    TypeCategory VARCHAR(50)
)
GO
CREATE TABLE Type2
( 
    TypeId INT IDENTITY(1,1) NOT NULL, --Compliant code (Ensure that TypeId column is not null)
    TypeName VARCHAR(50),
    TypeCategory VARCHAR(50)
)
GO

SELECT TypeId, TypeName, TypeCategory
FROM Type1  
WHERE TypeId NOT IN (SELECT TypeId FROM Type2)