NOT EXISTS Versus NOT IN With NULLs

SQLEXISTS vs INSeniorStaffUpdated Nov 10, 2026

Return the names of products that have NEVER been returned. The returns table can contain rows with product_id IS NULL.

Schema

CREATE TABLE products (
  id    BIGINT PRIMARY KEY,
  name  TEXT NOT NULL
);

CREATE TABLE returns (
  id         BIGINT PRIMARY KEY,
  product_id BIGINT  -- nullable: 'unknown product' returns
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (b)

SELECT name
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM returns r WHERE r.product_id = p.id
);

NOT EXISTS is NULL-safe: 'r.product_id = p.id' is simply false when r.product_id IS NULL, so those rows are ignored. NOT IN (a) returns zero rows because the subquery contains a NULL: 'x NOT IN (..., NULL)' evaluates to UNKNOWN, never true. (c) is a logic error: '!= ANY' is true if any value differs, so it is almost always true. (d) does not filter at all.

Going Deeper

Default to NOT EXISTS for anti-joins. If you must use NOT IN, either guarantee the subquery column is NOT NULL or wrap it: 'WHERE col NOT IN (SELECT x FROM t WHERE x IS NOT NULL)'.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT name
FROM products p
WHERE p.id NOT IN (SELECT product_id FROM returns);

Option (c)

SELECT name
FROM products p
WHERE p.id != ANY (SELECT product_id FROM returns);

Option (d)

SELECT name
FROM products p
LEFT JOIN returns r ON r.product_id = p.id;