On 8 June 2017 at 17:27, greigwise <greigwise@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
