Why does my SELECT query not return null values?
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