Isn't this the same as the NOT IN / NULL filter post?

That post is mostly != and NOT IN on a column. This one is the list side: IN (subquery) or IN (1, 2, NULL) where the list itself contains NULL. Same three-valued logic, different place people look.

What does IN (1, 2, NULL) actually mean?

id = 1 OR id = 2 OR id = NULL. The last term is unknown, not false. Rows that are 1 or 2 still match. Rows that are 3 become unknown (not in the result). NOT IN (1, 2, NULL) is worse: almost no row is definitely 'not in' a list that contains unknown.

When does a subquery put NULL in the list?

SELECT plan_id FROM experiments — plan_id is nullable. One leftover NULL in that table poisons NOT IN for everyone. IN still returns the definite matches; NOT IN can return nothing.

WHERE plan_id IS NOT NULL in the subquery. Or use EXISTS / NOT EXISTS, which don't treat a NULL in the other table as 'unknown for every row.'

Should I avoid IN entirely?

No. IN on a clean list of non-null IDs is fine. Avoid IN/NOT IN against a nullable column without filtering NULLs out first.

SQL IN List With NULL | Unknown Logic

You ask for "orders whose plan is in the experiment list." The experiment table has 40 plan ids and one NULL. NOT IN (SELECT plan_id FROM experiments) comes back empty. IN (...) looks a bit short. The AI did not typo the ids. A NULL inside the list is not a value. It is unknown, and it infects the comparison.

IN is OR; NULL is not false

WHERE plan_id IN (1, 2, NULL)
-- means: plan_id = 1 OR plan_id = 2 OR plan_id = NULL

plan_id = NULL is unknown, never true. So IN still finds 1 and 2. It does not mean "1, 2, or missing." For missing plans you still need OR plan_id IS NULL — the same rule as != skipping NULLs.

NOT IN is the landmine:

WHERE plan_id NOT IN (SELECT plan_id FROM experiments)
-- if any experiment.plan_id is NULL, this can return zero rows

No row is known to be outside a list that contains unknown. The query runs. The result is empty. No error.

That's why this is worth its own post: people check the column for NULL and miss the list.

The fix

-- clean the list
WHERE plan_id NOT IN (
  SELECT plan_id FROM experiments WHERE plan_id IS NOT NULL
)

-- or don't use IN for the anti-join
WHERE NOT EXISTS (
  SELECT 1 FROM experiments e
  WHERE e.plan_id = orders.plan_id
)

EXISTS compares row-to-row. A NULL plan_id on one experiment row does not poison every order.

How to catch it

If NOT IN (subquery) is empty and you know rows should match:

SELECT COUNT(*) FROM experiments WHERE plan_id IS NULL;

Non-zero? That's the bug. Same check for any IN (SELECT nullable_column ...).

The prompt that heads this off

If you use IN / NOT IN against a subquery, filter IS NOT NULL on that
column inside the subquery. Prefer NOT EXISTS for "not in this set."
If NOT IN returns zero rows, count NULLs in the list before changing
the join.

The short version

IN is not a bag of values once NULL is in the bag. NOT IN plus one NULL can delete the entire result. Filter NULLs out of the list, or use EXISTS.

Read-only MCP gateway: mcpserver.design.

Related reading: