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 VENDOR ( ID number(10) NOT NULL, NAME NVARCHAR2(75), ADDRESS, Landmark VARCHAR2, CONSTRAINT pk_vendor PRIMARY KEY (ID) ); SELECT NAME, ADDRESS, CREDIT_LIMIT FROM CUSTOMERS WHERE NAME NOT IN (SELECT NAME FROM VENDOR); --Non compliant code (NAME is null-able column)
Compliant Code Example
Create TABLE VENDOR ( ID number(10) NOT NULL, NAME NVARCHAR2(75), ADDRESS, Landmark VARCHAR2, CONSTRAINT pk_vendor PRIMARY KEY (ID) ); SELECT NAME, ADDRESS, CREDIT_LIMIT FROM CUSTOMERS WHERE NAME NOT IN (SELECT NAME FROM VENDOR WHERE NAME IS NOT NULL); --Compliant code (Where clause to avoid NULL name column)