From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | greigwise <greigwise(at)comcast(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Weirdness with "not in" query |
Date: | 2017-06-08 15:38:20 |
Message-ID: | ad6a9eb3-9070-3cc8-b87a-147bc445e47f@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 06/08/2017 08:27 AM, greigwise 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?
No:
Note that if the left-hand expression yields null, or if there are no
equal right-hand values and at least one right-hand expression yields
null, the result of the IN construct will be null, not false. This is in
accordance with SQL's normal rules for Boolean combinations of null values.
> Thanks,
> Greig Wise
>
>
>
> --
> View this message in context: http://www.postgresql-archive.org/Weirdness-with-not-in-query-tp5965573.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Eric Lemoine | 2017-06-08 16:00:04 | Performance issue with Pointcloud extension |
Previous Message | greigwise | 2017-06-08 15:35:37 | Re: Weirdness with "not in" query |