Re: NULL values in IN condition

From: Peter Nilsson <airia_at_acay.com.au>
Date: Thu, 11 Dec 2008 17:53:33 -0800 (PST)
Message-ID: <7a38043a-0ed6-4266-b0d7-c497cd0382be@t39g2000prh.googlegroups.com>

"Álvaro G. Vicario" <alvaroNOSPAMTHA..._at_demogracia.com> wrote:
> I want to obtain all the possible values of a VARCHAR2(1)
> column named FOO, sort them (either ASC or DESC) and finally
> fetch to two top-most rows. NULL values must be listed on
> top (ASC) or bottom (DESC). So I've written these queries:
>
> SELECT * FROM (
>         SELECT DISTINCT FOO
>         FROM TABLE
>         ORDER BY FOO NULLS FIRST
> ) WHERE ROWNUM<=2
> -- Returns: NULL, 'A'
>
> SELECT * FROM (
>         SELECT DISTINCT FOO
>         FROM TABLE
>         ORDER BY FOO DESC NULLS LAST
> ) WHERE ROWNUM<=2
> -- Returns: 'Y', 'Z'
>
> So far so good. Now I'd like to nest this subquery in an
> IN condition:
>
> SELECT *
> FROM ANOTHER_TABLE
> WHERE FOO_FK IN (.......)
>
> However, the IN condition always filters out NULL values.
> Something like FOO IN (NULL, 'A', 'B') is FALSE even when
> FOO is null; apparently, because FOO=NULL is not TRUE.
> But I need to consider rows with NULLs.
>
> I don't know before hand if there're NULLs in my "top 2",
> so...
>
>         WHERE FOO_FK IS NULL OR FOO_FK IN (.......)
>
> is not an option.
>
> Can you think of any trick to overcome this?
>
> The server runs Oracle 9.2.0.1.0
>
> -----
>
> Just before hitting "Send" I came to this idea:
>
> SELECT *
> FROM ANOTHER_TABLE
> WHERE NVL(FOO_FK, 'xxxxxxxxx') IN (
>         SELECT NVL(FOO, 'xxxxxxxxx') FROM (
>                 SELECT DISTINCT FOO
>                 FROM TABLE
>                 ORDER BY FOO NULLS FIRST
>         ) WHERE ROWNUM<=2
> )
>
> It works but... It looks so ugly! :-?

  with
    tofu as (select foo

               from (select distinct foo
                       from table
                      order by foo nulls first)
              where rownum<=2)

  select *
    from another_table t
   where exists

           (select 1
              from tofu tf
             where tf.foo = t.foo
                or tf.foo is null and t.foo is null)

--
Peter

Received on Thu Dec 11 2008 - 19:53:33 CST