Re: Select .... where id not in (....) returns 0 incorrectly
От | J. Roeleveld |
---|---|
Тема | Re: Select .... where id not in (....) returns 0 incorrectly |
Дата | |
Msg-id | 2245474.ElGaqSPkdT@iris обсуждение исходный текст |
Ответ на | Re: Select .... where id not in (....) returns 0 incorrectly (Jeremy Smith <jeremy@musicsmith.net>) |
Ответы |
Re: Select .... where id not in (....) returns 0 incorrectly
Re: Select .... where id not in (....) returns 0 incorrectly |
Список | 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
В списке pgsql-general по дате отправления: