mitchell vitez

up toggle dark mode

blog about music art media resume

email github

Null IN PostgreSQL

There’s an example in The Art of PostgreSQL1 that warns against writing some SQL like this:2

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.


  1. The Art of PostgreSQL. 2e. theartofpostgresql.com↩︎

  2. I tested these queries with this nifty online postgresql compiler running version 9.6.2↩︎