Thread: [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
> 
> 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

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