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)