agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedNot getting the expected results for a simple where not in
3+ messages / 2 participants
[nested] [flat]
* Not getting the expected results for a simple where not in
@ 2017-06-07 12:20 Jonathan Moules <jonathan-lists@lightpear.com>
0 siblings, 2 replies; 3+ messages in thread
From: Jonathan Moules @ 2017-06-07 12:20 UTC (permalink / raw)
To: pgsql-sql
Hi List,
I'm a little confused by what seems like it should be a simple query and was hoping someone could explain what's going on.
Using PG 9.4.x
CREATE TABLE aaa.testing_nulls
(
str character varying(10),
status character varying(2)
)
Data:
"first";"aa"
"second";"aa"
"third";null
"fourth";"bb"
null;"aa"
null;"bb"
If I run:
select
str
from
aaa.testing_nulls
where
status in ('aa')
Against the table, I get the expected result:
"first"
"second"
null
But I want to get the items that don't have a value of 'aa'. Obviously in this case I can simply add "not" to the "where status in" but that's not suitable for my actual use-case (which is where this problem came to light). Instead, I'm nesting the original as a subquery:
select
*
from
aaa.testing_nulls
where
str not in
(
select
str
from
aaa.testing_nulls
where
status in ('aa')
)
Conceptually to me at least, this should work. I expect to get the values:
"third"
"fourth"
But instead when I run it I get 0 results.
It seems to relate to the nulls. If I change the above and add "and str is not null" into the subquery:
select
*
from
aaa.testing_nulls
where
str not in
(
select
str
from
aaa.testing_nulls
where
status in ('aa')
and str is not null
)
It now gives the expected results.
Why is this?
(I tested this in SQLite too, and get the same behaviour, so I guess it's a generic SQL thing I've never encountered before.)
Thanks,
Jonathan
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Not getting the expected results for a simple where not in
@ 2017-06-07 12:49 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Jonathan Moules <jonathan-lists@lightpear.com>
1 sibling, 0 replies; 3+ messages in thread
From: Adrian Klaver @ 2017-06-07 12:49 UTC (permalink / raw)
To: Jonathan Moules <jonathan-lists@lightpear.com>; pgsql-sql
On 06/07/2017 05:20 AM, Jonathan Moules wrote:
> Hi List,
> I'm a little confused by what seems like it should be a simple query and
> was hoping someone could explain what's going on.
> Using PG 9.4.x
>
> CREATE TABLE aaa.testing_nulls
> (
> str character varying(10),
> status character varying(2)
> )
>
> Data:
> "first";"aa"
> "second";"aa"
> "third";null
> "fourth";"bb"
> null;"aa"
> null;"bb"
>
> If I run:
> select
> str
> from
> aaa.testing_nulls
> where
> status in ('aa')
>
> Against the table, I get the expected result:
> "first"
> "second"
> null
>
> But I want to get the items that don't have a value of 'aa'. Obviously
> in this case I can simply add "not" to the "where status in" but that's
> not suitable for my actual use-case (which is where this problem came to
> light). Instead, I'm nesting the original as a subquery:
>
> select
> *
> from
> aaa.testing_nulls
> where
> str not in
> (
> select
> str
> from
> aaa.testing_nulls
> where
> status in ('aa')
> )
>
> Conceptually to me at least, this should work. I expect to get the values:
> "third"
> "fourth"
> But instead when I run it I get 0 results.
>
> It seems to relate to the nulls. If I change the above and add "and str
> is not null" into the subquery:
>
> select
> *
> from
> aaa.testing_nulls
> where
> str not in
> (
> select
> str
> from
> aaa.testing_nulls
> where
> status in ('aa')
> and str is not null
> )
>
> It now gives the expected results.
> Why is this?
https://www.postgresql.org/docs/9.6/static/functions-subquery.html#FUNCTIONS-SUBQUERY-IN
"Note that if the left-hand expression yields null, or if there are no
equal right-hand values and at least one right-hand row yields null, the
result of the NOT IN construct will be null, not true. This is in
accordance with SQL's normal rules for Boolean combinations of null values."
> (I tested this in SQLite too, and get the same behaviour, so I guess
> it's a generic SQL thing I've never encountered before.)
> Thanks,
> Jonathan
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Not getting the expected results for a simple where not in
@ 2017-06-07 13:28 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Jonathan Moules <jonathan-lists@lightpear.com>
1 sibling, 0 replies; 3+ messages in thread
From: Adrian Klaver @ 2017-06-07 13:28 UTC (permalink / raw)
To: Jonathan Moules <jonathan-lists@lightpear.com>; pgsql-sql
On 06/07/2017 05:20 AM, Jonathan Moules wrote:
> Hi List,
> I'm a little confused by what seems like it should be a simple query and
> was hoping someone could explain what's going on.
> Using PG 9.4.x
>
> It seems to relate to the nulls. If I change the above and add "and str
> is not null" into the subquery:
>
> select
> *
> from
> aaa.testing_nulls
> where
> str not in
> (
> select
> str
> from
> aaa.testing_nulls
> where
> status in ('aa')
> and str is not null
> )
>
> It now gives the expected results.
Or you could do:
select
*
from
testing_nulls
where
str not in
(
select
coalesce(str, '')
from
testing_nulls
where
status in ('aa')
)
;
str | status
--------+--------
third | NULL
fourth | bb
(2 rows)
> Why is this?
> (I tested this in SQLite too, and get the same behaviour, so I guess
> it's a generic SQL thing I've never encountered before.)
> Thanks,
> Jonathan
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-06-07 13:28 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-06-07 12:20 Not getting the expected results for a simple where not in Jonathan Moules <jonathan-lists@lightpear.com>
2017-06-07 12:49 ` Adrian Klaver <adrian.klaver@aklaver.com>
2017-06-07 13:28 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox