Search code examples
postgresqlnullnotin

Postgres NOT IN (null) gives no result

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

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