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;
Top comments (1)
Boolean logic is built around the binary values
TRUEandFALSE. SQL, however, is built around a three-valued logic:TRUE,FALSE, andNULL, 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 thatNULLcannot 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.