Home

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)