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

From: Jeremy Smith <jeremy(at)musicsmith(dot)net>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Select .... where id not in (....) returns 0 incorrectly
Date: 2022-04-04 12:50:44
Message-ID: CAM8SmLWx8b4kZN64wR2iYtQEKuSmYEXS7E42c+D5OPxdyWx9iQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message J. Roeleveld 2022-04-04 13:21:07 Re: Select .... where id not in (....) returns 0 incorrectly
Previous Message Ravi Krishna 2022-04-04 12:44:15 Re: Select .... where id not in (....) returns 0 incorrectly