Re: [GENERAL] Weirdness with "not in" query
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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Weirdness with "not in" query
Wow. That is exactly it. Thank you. I really would not have expected there to be NULLs in that field. Geez. -- View this message in context: http://www.postgresql-archive.org/Weirdness-with-not-in-query-tp5965573p5965576.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Weirdness with "not in" query
On 8 June 2017 at 17:27, 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? 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Weirdness with "not in" query
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? 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
