Home
Nullable subqueries should not be used in NOT IN conditions
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)