Home

Description

The rule "Nullable subqueries should not be used in NOT IN conditions" states that when using a NOT IN condition in a PL/SQL statement, the subquery should not return any null values. This is because when a subquery returns a null value, the NOT IN condition will always evaluate to false, even if the value being compared is not in the subquery. This can lead to unexpected results and should be avoided.

Key Benefits

• Eliminates the need for complex subqueries - Nullable subqueries are not required in NOT IN conditions, making the query simpler and easier to read.
• Improves query performance - By eliminating the need for complex subqueries, the query can be executed faster.
• Ensures accuracy - By ensuring that only valid data is returned, the query results are more accurate.

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)