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

Solution 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)

Solution 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.

Solution 3

answer

The solution was already posted ^{in this answer}, 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,              -- true
    1 = 2,              -- false
    -- "simple things" with null
    1  = null,          -- null (and it is not `false` if you expected this)
    1 != null,          -- null (and it is not `true` if you expected this)
    -- "strange" area:
    null  = null,       -- null (and it is not `true` and not `false`)
    null != null,       -- null (and it is not `true` and not `false`)
    1 > null,           -- null (yeah, after 4 previous examples it is exactly what you expected)
    1 < null,           -- null
    null < null,        -- null
    null > null,        -- null
    -- value IN ()
    1 in (1,2),         -- true
    1 in (1,null),      -- true
    1 in (2, 3),        -- false
    1 in (2, null),     -- !!! null
    -- value NOT IN
    1 not in (1,2),     -- false
    1 not in (1,null),  -- false
    1 not in (2, 3),    -- true
    1 not in (2, null), -- !!! null
    -- NULL IN/NOT IN
    null in (1,2),      -- null
    null in (NULL),     -- null
    null not in (1,2),  -- null
    null not in (NULL), -- null
    -- and surprise:
    NOT NULL            -- !! NULL (but most probably you foresaw/knew this)

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

Solution 4

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

"IN" or "NOT IN" do not select NULL values You can write

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

And your selection will not contains null values

View more solutions

Comments

[wutzebaer]

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

[IMSoP]

Welcome! This answer seems to repeat the information already included in the answers elsewhere on this page, rather than representing a new answer to the question. If you haven't already, you might want to look at the tour which explains a bit about how this site works.

[Agony]

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

[maxkoryukov]

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 ) ).

[a_horse_with_no_name]

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.

[wutzebaer]

=/ this links exactly here?

[maxkoryukov]

> link is here — yes, that man posted his answer first, so I added a link to his answer (which is actually the same as mine, or mine is the same as his), I just added some notes on how PG-operations handle NULL-operands

[Rustem Zinnatullin]

This in (null) behavior is quite confusing. select * from Table where Table.id in ('abc') or not Table.id in ('abc') will not return records with id = null, however, logically, it should return everything.