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:
https://www.postgresql.org/docs/9.6/static/functions-comparisons.html#FUNCTIONS-COMPARISONS-IN-SCALAR
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.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
