Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] Weirdness with "not in" query

[GENERAL] Weirdness with "not in" query

From
greigwise
Date:
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.

Re: [GENERAL] Weirdness with "not in" query

From
Alban Hertroys
Date:
On 8 June 2017 at 17:27, greigwise <greigwise@comcast.net> 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.

Re: [GENERAL] Weirdness with "not in" query

From
greigwise
Date:
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.

Re: [GENERAL] Weirdness with "not in" query

From
Adrian Klaver
Date:
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@aklaver.com