Name: Why postgresql does not return null values once filtered by not_in
Rating: 2.4 (2274 reviews)
Author: pcmagas

Why postgresql does not return null values once filtered by not_in

Dimitrios Desyllas - Jul 2 '20 -

- Dev Community

As I mention on https://dba.stackexchange.com/q/270265/118215

I noticed that not in fails to retrieve rows that have null values as this script says so:

CREATE temporary table if not EXISTS the_values (
 key SERIAL,
 value INTEGER NULL 
);

insert into the_values(value) values (null),(1),(null),(2),(3),(4),(5),(6),(10),(null),(null);


select * from the_values where value not in (1,2,3,4,5,6,10);

Specifically the query:

select * from the_values where value not in (1,2,3,4,5,6,10);

Therefore, do you have any idea why that happens? I am interested more about the technical aspect of this phenomenon rather that the obvious solution:

select * from the_values where value not in (1,2,3,4,5,6,10) or value IS NULL;