Critical

Rule description

• Nullable subqueries should not be used in NOT IN conditions

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)