From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | greigwise <greigwise(at)comcast(dot)net> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Weirdness with "not in" query |
Date: | 2017-06-08 15:33:13 |
Message-ID: | CAF-3MvN8XmZ2aPRoz4KkpFsvjb0Uhvv0ta4zxMWt9rg8X-N5XA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 8 June 2017 at 17:27, greigwise <greigwise(at)comcast(dot)net> wrote:
> So, I'm using postgres version 9.6.3 on a mac and the results to this series
> of queries seems very strange to me:
>
> db# select count(*) from table1 where id in
> (1706302,1772130,1745499,1704077);
> count
> -------
> 4
> (1 row)
>
> db# select count(*) from table2 where table1_id in
> (1706302,1772130,1745499,1704077);
> count
> -------
> 0
> (1 row)
>
> db# select count(*) from table1 where id not in (select table1_id from
> table2);
> count
> -------
> 0
> (1 row)
>
> I would expect the "not in" query to return a result of at least 4. Am I
> totally misunderstanding how this should work (I really don't think so) or
> is something wrong?
You probably have table1_id's that are NULL in table2. In that case
the result of not in is null as well.
Not exists is perhaps a better candidate in this case.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
From | Date | Subject | |
---|---|---|---|
Next Message | greigwise | 2017-06-08 15:35:37 | Re: Weirdness with "not in" query |
Previous Message | greigwise | 2017-06-08 15:27:52 | Weirdness with "not in" query |