Search Postgresql Archives

Re: Weirdness with "not in" query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux