Null IN PostgreSQL

There’s an example in The Art of PostgreSQL¹ that warns against writing some SQL like this:²

select x
from (1, 2, 3, 4, 5) as temp(x)
where x not in (1, 2, null);

In case you’re not familiar with SQL, this is roughly saying: “take all the numbers x from the list (1, 2, 3, 4, 5), which we’ll call temp, where that number x is not in the list (1, 2, null)”.

I say roughly because that’s not really what this does at all. This query returns no rows! Let’s explore why. Here’s an example of an IN statement working as expected:

select 7 in (7); -- True

Indeed, 7 is in the list (7), so this should be true. Likewise,

select 7 in (8); -- False
select 7 not in (7); -- False

7 is not in the list (8), and expecting 7 to not be in the list (7) is also going to lead to disappointment. So far so good.

What would you expect the following query to produce?

select null in (7);

Naively, since null is not in the list 7, we might expect False. However, null is not really a value in this sense, and has its own special semantics. Instead this query itself returns null.

Likewise, looking for something in a list that contains only null will return null.

select 7 in (null); -- NULL

Looking for something in a list not containing that thing, but containing null, will return null

select 7 in (8, 9, null); -- NULL

Finally, looking for something in a list that contains it, but also null, will return True! This is true independent of the item’s position in the list.

select 7 in (7, null); -- True
select 7 in (null, 7); -- True

So what’s really going on in our initial query, is that 1 and 2 are found in the list, but the not in inverts them to false, so they don’t show up in the result. And since 3, 4, and 5 are not in the list, but the list contains null, they also won’t show up, since they’ll be null.

select x
from (1, 2, 3, 4, 5) as temp(x)
where x not in (1, 2, null);

So we have a big list of Falses and nulls and in the end the query has an empty result! Do not treat null as a value, do not pass go, do not collect $200.