Use NOT IN Safely When NULLs Are Possible

Filter `NULL` values out of the right side before using `NOT IN`, or the predicate can exclude everything.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Exclude IDs from a list that contains NULL

Alice is excluded because her ID is present in the blacklist. Bob and Carol remain. The critical detail is WHERE customer_id IS NOT NULL inside the subquery. Without that filter, the NULL in blacklist would make NOT IN behave unexpectedly and could produce no rows at all.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE customers (id INT, name VARCHAR(50));

CREATE TABLE blacklist (customer_id INT);

INSERT INTO
  customers (id, name)
VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Carol');

INSERT INTO
  blacklist (customer_id)
VALUES
  (1),
  (NULL);

Validated query Shared across supported engines.

SQL

SELECT
  name
FROM
  customers
WHERE
  id NOT IN (
    SELECT
      customer_id
    FROM
      blacklist
    WHERE
      customer_id IS NOT NULL
  )
ORDER BY
  id;

Expected result Returned rows for the shared example.

name
Bob
Carol

The example filters out `NULL` inside the subquery to keep `NOT IN` trustworthy.

Useful when

Where this command helps.

excluding rows based on a lookup table that may contain missing values
avoiding empty results from a `NOT IN` subquery

Explanation

What the command is doing.

NOT IN has a surprising interaction with NULL. If the list or subquery on the right side contains a NULL, each comparison becomes unknown instead of true, so rows you expected to keep may disappear. The safe pattern is to remove NULL values inside the subquery before applying NOT IN, or use NOT EXISTS instead.

Use NOT IN Safely When NULLs Are Possible

Exclude IDs from a list that contains NULL

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.