From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Jonathan Moules <jonathan-lists(at)lightpear(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Not getting the expected results for a simple where not in |
Date: | 2017-06-07 13:28:43 |
Message-ID: | d2a16055-efdd-d502-d1df-1fd36804e066@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Majid Khan | 2017-06-12 07:53:34 | crosstab category mix |
Previous Message | Adrian Klaver | 2017-06-07 12:49:43 | Re: Not getting the expected results for a simple where not in |