Why does my SELECT query not return null values? I have the following SQL script:
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); 

And I noticed that the query:
select * 
from the_values 
where value not in (1,2,3,4,5,6,10); 

Does not return the rows having value NULL, and that caught my attention. Therefore, I want to know 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;
Generally, avoid NOT IN when NULL values can be involved on either side. The Postgres Wiki suggests as much. And avoid NOT IN (SELECT ...) in any case. The Wiki has the explanation. And also happens to have the perfect answer to your core question:


NOT IN behaves in unexpected ways if there is a null present:



select * from foo where col not in (1,null); -- always returns 0 rows



select * from foo where col not in (select x from bar);   -- returns 0 rows if any value of bar.x is null



This happens because col IN (1,null) returns TRUE if col=1, and NULL
otherwise (i.e. it can never return FALSE). Since NOT (TRUE) is FALSE,
but NOT (NULL) is still NULL, there is no way that NOT (col IN (1,null)) (which is the same thing as col NOT IN (1,null)) can return
TRUE under any circumstances.

Here is a maybe not so obvious solution:
SELECT * 
FROM   the_values 
WHERE  value IN (1,2,3,4,5,6,10) IS NOT TRUE;

Or:
..
WHERE  value = ANY('{1,2,3,4,5,6,10}') IS NOT TRUE;

It's shorter and typically faster than your &quot;obvious&quot; one.
For long lists, consider switching to a different (faster) technique:

Select rows which are not present in other table