Re: Select .... where id not in (....) returns 0 incorrectly - Mailing list pgsql-general

On Monday, April 4, 2022 2:50:44 PM CEST Jeremy Smith wrote:
> > Doesn't this usually happen if q.id contains NULL.  That is as per ANSI
> > standard.
> 
> Yes, there's a good description of this here:
> https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_NOT_IN
> 
> It would be better to use NOT EXISTS:
> select count(*) from snapshotlist where NOT EXISTS (SELECT FROM q WHERE q.id
> = snapshotlist.id);
> 
> 
> -Jeremy

Thank you Jeremy and Ravi.

This was actually the case, I forgot there is 1 NULL-value in that list.
Personally, I think NULL should be treated as a seperate value and not lead to 
strange behaviour.

I will need to look into my queries and get rid of "NOT IN" constructions when 
the list comes from a different query.

--
Joost