loading...

Why postgresql does not return null values once filtered by not_in

pcmagas profile image Dimitrios Desyllas ・1 min read

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; 

Discussion

markdown guide
 

Boolean logic is built around the binary values TRUE and FALSE. SQL, however, is built around a three-valued logic: TRUE, FALSE, and NULL, where the last is an unknown or undefined value. For more detail you could consult Wikipedia or appendix D in de Haan and Koppelaars' Applied Mathematics for Database Professionals, but the short version is that NULL cannot be compared to a known value: any comparison with an unknown value yields an unknowable result.

Postgres has a shortcut: x IS [NOT] DISTINCT FROM y, although that's likely not much help here with a list of values.