NULL values in IN condition

From: Álvaro G. Vicario <alvaroNOSPAMTHANKS_at_demogracia.com>
Date: Thu, 11 Dec 2008 17:54:54 +0100
Message-ID: <ghrgku$g2m$1@huron.algomas.org>

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
  --
  

Received on Thu Dec 11 2008 - 10:54:54 CST