Postgres NOT IN (null) gives no result

Question

I'm using Postgres with this query

select 
*
from Entity this_ 
where 
(this_.ID not in (null))

Why does this give me no results? I would expect to get all rows where id is not null

with

(this_.ID not in (1))

i get the expected results

Lection

Answer 1

The result of [not] in (null) will always be null. To compare to null you need is [not] null or is [not] distinct from null

select *
from Entity this_ 
where this_.ID is not null

If you want where (ID not in (1,null)) as in your comment you can do

where ID is not null and ID not in (1)

Hackler

means a select like "select * from Entity where (ID not in (1,null))" is impossible? – Lection

I spent hours trying to figure why I didn't get what I expected from a query with something like "id not in (select id from ...)" in the where clause. I can't think of a case in which such behave would be useful or make sense. null should not match anything (maybe only null in some cases), but in such cases, seems null matched anything. – Mintun

What the hel is this? Why the hack in () clause suppose null to be value included in the range? It does not make basic sense. – Cassandra

Answer 2

PostgreSQL uses NULL as undefined value.
What you're asking is to return the items that are not in a list or an undefined value. Since undefined means that you do not know what's inside, PostgreSQL does not return any item because simply can not respond to the request.
While the request:

select * from Entity where id in (1, null)

can return the records, because if it finds an element with ID = 1 knows that is in the collection
the request:

select * from Entity where (ID not in (1, null))

can not be satisfied because the null value can be any value.

Workbook

the explanation is good, but NULL is NULL, and not any value. It is a special value but definitely not ANY value. Actually, for me it was enough to remember: where null = null - returns 0 lines, where null != null - 0 lines, null > null - zero lines, and almost all other operations with NULL as argument returns false. all these work for the IN-operator as well (because it is basically almost the same as comparing == the item to every element in the ( parentheses - list ) ). – Perfumery

actually, most operations with NULL return NULL (not false) sorry for a misleading comment – Perfumery

Answer 3

answer

The solution was already posted ^{another answer, but the same page}, and my solution is almost the same:

where
  "field" is NOT NULL
  AND "field" not in (1)

don't forget, the inverted version of the condition (is null or in (list)) uses the OR-operator (instead of AND):

where
  "field" is NULL
  OR "field" in (1)

bonus: SQL and NULL

and here is a thing a good SQL-developer has somewhere in the subconscious zone (the request was tested in Postgres, but I'm pretty sure it is the behavior from the standard ANSI SQL):

SELECT

-- simple things
1 = 1 as "1 = 1",                                 -- true  
1 = 2 as "1 = 2",                                 -- false 

-- "simple things" with null
1  = NULL as "1  = NULL",                         -- null  -- (and it is not `false` if you expected this)
1 != NULL as "1 != NULL",                         -- null  -- (and it is not `true` if you expected this)

-- "strange" area
NULL  = NULL as "NULL  = NULL",                   -- null  -- (and it is not `true` and not `false`)
NULL != NULL as "NULL != NULL",                   -- null  -- (and it is not `true` and not `false`)
1 > NULL as "1 > NULL",                           -- null  -- (yeah, after 4 previous examples it is exactly what you expected)
1 < NULL as "1 < NULL",                           -- null  
NULL < NULL as "NULL < NULL",                     -- null  
NULL > NULL as "NULL > NULL",                     -- null  

-- value IN ()
1 in (1,2) as "1 in (1,2)",                       -- true  
1 in (1,NULL) as "1 in (1,NULL)",                 -- true  
1 in (2, 3) as "1 in (2, 3)",                     -- false 
1 in (2, NULL) as "1 in (2, NULL)",               -- null  -- (!!! surprise?)
3 between 1 AND NULL as
  "3 between 1 AND NULL",                         -- null  

-- value NOT IN
1 not in (1,2) as "1 not in (1,2)",               -- false 
1 not in (1,NULL) as "1 not in (1,NULL)",         -- false 
1 not in (2, 3) as "1 not in (2, 3)",             -- true  
1 not in (2, NULL) as "1 not in (2, NULL)",       -- null  -- (!!! surprise?)

-- NULL IN/NOT IN
NULL in (1,2) as "NULL in (1,2)",                 -- null  
NULL in (NULL) as "NULL in (NULL)",               -- null  
NULL not in (1,2) as "NULL not in (1,2)",         -- null  
NULL not in (NULL) as "NULL not in (NULL)",       -- null  

-- bonus -surprise
not NULL as "not NULL",                           -- null  -- (!!, but most probably you foresaw/knew this)

-- <IS NOT DISTINCT FROM> (acts like === in JS, or strict equality)
1 is not distinct from 1 as
  "1 is not distinct from 1",                     -- true  
1 is not distinct from 2 as
  "1 is not distinct from 2",                     -- false 
1 is not distinct from null as
  "1 is not distinct from null",                  -- false 
null is not distinct from null as
  "null is not distinct from null",               -- true  -- !! this is the important difference
NULL AS "NULL"

as you can see - if the null is an operand then the result is null and in a boolean context (for example, in WHERE-clauses) - it is falsey. Though, falsey is not the same as false, and NOT (1=NULL) is NULL, but not truly, so both these requests return 0 lines:

-- 1
select 1 where (1 = null)

-- 2
select 1 where NOT (1 = null)

I hope it was useful

Perfumery

=/ this links exactly here? – Lection

yes, the link points to another answer ON THIS PAGE/under this question. AndreaBoc gave the response, I added more details and examples – Perfumery

Answer 4

I have had similar in problem and eventually came up with the following solution;

select * from public."Employee_11" where (COALESCE("Name",'@'),"Surname") 
    in (
        ('@','dummy')
    )

It does return the records which Name column has null values. You can also use this for not in clause which will return not null records of Name;

  select * from public."Employee_11" where (COALESCE("Name",'@'),"Surname") 
        not in (
            ('@','dummy')
        )

Arlindaarline

Answer 5

I had similar problem. My ego that I knew SQL well, got punctured. Here is the simplified example from scott/tiger tables.

select empno, ename from emp where deptno not in (10, 20, null);

Returned nothing. Although I use NOT IN condition very sparingly because it does not use index and is very slow. I prefer to use OUTER JOIN instead.

I tried this query in Postgres and Oracle both and results are the same. So, must be a standards compliant result. NULL behaves this way only in NOT IN condition.

Abomination

All DBMS behave this way. deptno not in (10, 20, null); is the same as detpno <> 10 AND detpno <> 20 AND deptno <> NULL which is (e.g. for detpno=1) the same as false AND false AND null which in turn is NULL - which in turn is "not true" for the WHERE clause. The result is independent of the value of deptno and thus it's "not true" for all rows in the table and consequently it returns nothing. – Coom

Answer 6

You can use <> ANY operator. Example on your code:

select 
  * 
from Entity this_ 
where 
   (this_.ID <> ANY (null))

Mcquoid

this behaves just like NOT IN, null does return anything. – Gounod