NULL values in IN condition

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!



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

"Álvaro G. Vicario" 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