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 selected
SELECT 'X' FROM DUAL 
WHERE COALESCE(NULL,'') IN ('','B','C','D'); 
no rows selected

Further, the following SQL shows that Oracle (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production) treats '' as NULL:

SELECT NVL(COALESCE(NULL,''),'XXX') FROM DUAL
NVL
---
XXX

So, is this behavior not carried forward ONLY 10g onwards?

Thanks

    Requires Free Membership to View

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:

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

This was first published in September 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: