This is with reference to NULL can be your friend (18 August 2005). You suggest to use COALESCE function and say...
that "What this COALESCE expression does is substitute an empty string whenever foo is NULL, so that the resulting value will be found in the IN list."
However, my results are contrary:
SELECT 'X' FROM DUAL WHERE COALESCE('foo','') IN ('','B','C','D'); no rows selectedSELECT 'X' FROM DUAL WHERE COALESCE(NULL,'') IN ('','B','C','D'); no rows selected
Further, the following SQL shows that Oracle (Oracle9i Enterprise Edition Release 22.214.171.124.0 - Production) treats '' as NULL:
SELECT NVL(COALESCE(NULL,''),'XXX') FROM DUALNVL --- XXX
So, is this behavior not carried forward ONLY 10g onwards?
The behavior that you have discovered is well-known. Oracle treats an empty string as NULL. This is not standard SQL.
Apparently, this behavior continues. The Oracle 10g Release 1 documentation for Nulls contains this note:
Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.
So to come back to the example given, you could also get the desired results by substituting something else for the empty string, like this:
WHERE COALESCE(foo,'~') IN ('~','B','C','D')
The point was to ensure that the result of the WHERE condition will evaluate TRUE both for NULLs as well as for the legitimate values 'B', 'C', and 'D'.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading