Re: [SQL] Not getting the expected results for a simple where not in

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