Why SQL NOT IN returns no rows when the column has NULL

You write a query to drop a few blocked customers. The blocked list has two real ids and one NULL. You expect everyone else. You get nothing.

That is the NOT IN plus NULL trap. It shows up in interviews and in production reports. Here it is on a small SQLite dataset, with every result taken from a real run on Python 3.13's sqlite3.

The tiny dataset

Five customers. One of them has no city yet.

SELECT id, name, city FROM customers ORDER BY id;

1 | Asha | Pune
2 | Ravi | Delhi
3 | Meera | NULL
4 | Kabir | Pune
5 | Neha | Mumbai

A blocked list. Two real ids, and one NULL (a bad import, a left join that leaked, a nullable column you forgot about).

SELECT customer_id FROM blocked;

2
4
NULL

The query that returns nothing

You want customers who are not blocked:

SELECT id, name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM blocked)
ORDER BY id;

(no rows)

No rows. Asha, Meera, and Neha should have come back. They did not.

The same thing happens with a literal list that contains NULL:

SELECT id, name
FROM customers
WHERE id NOT IN (2, 4, NULL)
ORDER BY id;

(no rows)

Still nothing. So this is not a subquery quirk. It is how NOT IN works once NULL is in the list.

What NOT IN really means

x NOT IN (a, b, c) is the same as:

x <> a AND x <> b AND x <> c

SQL comparisons against NULL are not true and not false. They are unknown. AND needs every part to be true. One unknown makes the whole thing unknown. WHERE only keeps rows where the condition is true. Unknown rows are dropped.

You can probe that with simple expressions. SQLite prints 0 for false, 1 for true, and NULL for unknown:

SELECT
  5 = 2,
  5 = NULL,
  5 <> 2,
  5 <> NULL,
  (5 <> 2) AND (5 <> NULL);

5 = 2 -> 0
5 = NULL -> NULL
5 <> 2 -> 1
5 <> NULL -> NULL
(5 <> 2) AND (5 <> NULL) -> NULL

So for Neha (id 5):

• 5 <> 2 is true
• 5 <> 4 is true
• 5 <> NULL is unknown

true AND true AND unknown is unknown. Neha is out. The same unknown hits every other id. The whole result set vanishes.

Three ways that work

1. Drop NULLs from the list

If you still want NOT IN, keep only known ids in the subquery:

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

1 | Asha
3 | Meera
5 | Neha

Asha, Meera, Neha. That matches what you meant.

2. Prefer NOT EXISTS

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM blocked b
  WHERE b.customer_id = c.id
)
ORDER BY c.id;

1 | Asha
3 | Meera
5 | Neha

Same three names. A NULL in blocked.customer_id never equals c.id, so it does not match, and the customer stays. This is usually the safer pattern for "rows in A with no match in B".

3. LEFT JOIN, then keep the misses

SELECT c.id, c.name
FROM customers c
LEFT JOIN blocked b ON b.customer_id = c.id
WHERE b.customer_id IS NULL
ORDER BY c.id;

1 | Asha
3 | Meera
5 | Neha

Again the same three. The join finds no partner for Asha, Meera, and Neha. Filtering on b.customer_id IS NULL keeps those misses. (A NULL sitting alone in blocked does not join to anyone, so it does not remove extra rows.)

The cousin trap: = NULL

The same three-valued idea breaks a different beginner query. Meera has a NULL city. This looks like it should find her:

SELECT id, name, city
FROM customers
WHERE city = NULL
ORDER BY id;

(no rows)

Nothing. city = NULL is unknown for every row, including Meera. Use IS NULL:

SELECT id, name, city
FROM customers
WHERE city IS NULL
ORDER BY id;

3 | Meera | NULL

What to say in an interview

If someone asks why NOT IN returned an empty set, say:

1. NOT IN expands to a chain of <> checks joined by AND.
2. Any compare with NULL is unknown, not false.
3. WHERE drops unknown.
4. One NULL in the list is enough to drop every candidate row.
5. Fix with WHERE col IS NOT NULL inside the subquery, or switch to NOT EXISTS / LEFT JOIN.

Then show a three-row table with one NULL in the list, like the demo above. That usually settles it.

A habit that saves you later

When you write NOT IN against a nullable column, either filter the NULLs or don't use NOT IN. Treat a nullable subquery column as a bug waiting for one bad import. The query that "worked in staging" can go quiet the day NULL shows up.

I work on Schoolabe, a free coding practice site. Short SQL drills like this live in the SQL learning section.