· Development

Why NOT IN returns no rows: SQL NULL and three-valued logic

One NULL in a subquery makes NOT IN return zero rows. Why, per the PostgreSQL, MySQL and SQLite docs: three-valued logic, how WHERE keeps rows, and fixing it with NOT EXISTS.

DevelopmentDatabasesSQL

A common way to pick users who are not on a block list looks like this:

SELECT id FROM users
WHERE id NOT IN (SELECT user_id FROM blocked);

If blocked.user_id holds a NULL in even one row, this query returns nothing. There is no error and no warning. The cause is that SQL evaluates comparisons with three outcomes, not two: true, false, and unknown.

This post is based on the official PostgreSQL, MySQL and SQLite documentation. The ISO SQL standard itself was not consulted. The PostgreSQL docs describe the subquery form of NOT IN behaving this way and say it follows SQL’s normal rules for combining NULLs in Boolean expressions. The MySQL manual separately documents the same NULL rule for the value-list form of IN() and says this is to comply with the SQL standard.

Comparing with NULL gives NULL

According to the PostgreSQL docs, ordinary comparison operators return NULL, meaning “unknown”, rather than true or false when either input is NULL. Both 7 = NULL and 7 <> NULL are NULL. That is also why you test for NULL with IS NULL, not = NULL.

NULL propagates through logical operators as follows:

Expression Result
TRUE AND NULL NULL
FALSE AND NULL FALSE
NOT NULL NULL

Expanding NOT IN

The PostgreSQL docs describe NOT IN over a value list as shorthand for <> comparisons joined with AND. So 1 NOT IN (2, NULL) expands to:

1 <> 2 AND 1 <> NULL
-- TRUE AND NULL → NULL

The first comparison is true, but the second is NULL, so the whole expression is NULL. For 2 NOT IN (2, NULL), 2 <> 2 is false, so the whole expression is false. Once the right-hand side contains a NULL, NOT IN can only be false or NULL. It is never true.

The rule is the same with a subquery. The PostgreSQL docs state that if there are no equal values and at least one row of the subquery yields NULL, NOT IN returns NULL, not true. The MySQL manual says IN() returns NULL when no match is found and the list contains a NULL, and defines NOT IN as NOT (expr IN (...)). The result table in the SQLite docs agrees.

WHERE keeps only true rows

According to the PostgreSQL docs, WHERE keeps a row only when the condition is true. Rows where it is false or NULL are discarded. A NOT IN with a NULL on the right is false or NULL for every row, so nothing comes back.

IN rarely shows the problem. With id IN (2, NULL), the row with id 2 is true and stays; every other row is NULL and is dropped. Those rows would have been dropped as false anyway, so the result looks normal. The PostgreSQL docs also note that NULLs trip people up far more often with NOT IN than with IN, and suggest expressing the condition positively where possible.

Reproduced in SQLite

The following was actually run on SQLite 3.43.2.

CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE blocked(user_id INTEGER);

INSERT INTO users VALUES (1,'a'), (2,'b'), (3,'c');
INSERT INTO blocked VALUES (2), (NULL);
Query Result
WHERE id NOT IN (SELECT user_id FROM blocked) no rows
WHERE id IN (SELECT user_id FROM blocked) 2
NOT EXISTS 1, 3
NOT IN with WHERE user_id IS NOT NULL in the subquery 1, 3

How to fix it

The first fix is to use NOT EXISTS. According to the PostgreSQL docs, EXISTS is true if the subquery returns at least one row and false if it returns none. The SQLite docs likewise say EXISTS always evaluates to 0 or 1 and does not treat rows containing NULL differently. Since its result can never be NULL, the trap above cannot happen.

SELECT id FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM blocked b WHERE b.user_id = u.id
);

The second fix is to filter NULLs out of the subquery.

SELECT id FROM users
WHERE id NOT IN (
  SELECT user_id FROM blocked
  WHERE user_id IS NOT NULL
);

When the left-hand value can be NULL, the two fixes differ

The PostgreSQL docs say NOT IN returns NULL when the left-hand value is NULL, so a row whose id is NULL is dropped by the second fix. With NOT EXISTS, the comparison b.user_id = NULL is never true for any row, so the subquery returns nothing and the row is kept.

The users table above cannot show this. According to the SQLite docs, an INTEGER PRIMARY KEY column in an ordinary table cannot hold NULL. Inserting NULL into it makes SQLite pick an integer value automatically. A separate table that allows NULL was therefore created and queried on SQLite 3.43.2.

CREATE TABLE nullable_users(id INTEGER, name TEXT);
INSERT INTO nullable_users VALUES (1,'a'), (2,'b'), (NULL,'x');

-- Fix 2
SELECT id, name FROM nullable_users
WHERE id NOT IN (
  SELECT user_id FROM blocked
  WHERE user_id IS NOT NULL
);

-- Fix 1
SELECT id, name FROM nullable_users n
WHERE NOT EXISTS (
  SELECT 1 FROM blocked b WHERE b.user_id = n.id
);
Query Result (id, name)
Fix 2: NOT IN with IS NOT NULL (1, a)
Fix 1: NOT EXISTS (1, a), (NULL, x)

Which result is correct depends on what you need, so check whether the left-hand column can be NULL before you switch.

Documentation used

Links and content were checked on October 4, 2026.

Back to the blog