Re: Select .... where id not in (....) returns 0 incorrectly

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