Oracle implements many convenient functions to handle NULL values in the database, one of which is the COALESCE function. The Oracle COALESCE function accepts a varying length list of arguments and returns the first non-NULL value in the list. If all arguments in the list evaluate to a NULL value, then the COALESCE function will return a NULL value.
The standard format for the COALESCE function is:
COALESCE(arg1, arg2, ..., argN)
There are many practical uses for the Oracle COALESCE function, two common actions include:
- Passing in a list of common items, which may or may not have data in them in a hope you’ll get back something which you can use.
- Generating a default value for a field which contains
NULLvalues.
As a simple, yet practical example of how to use the COALESCE function; following demonstrates how to substitute in a default value in a SELECT statement when there are NULL values present:
SELECT COALESCE(ADDRESS2, 'EMPTY') FROM ADDRESSES
Since there is always more than one way to achieve the same outcome, the following two statements will return the same results as the previous example:
SELECT DECODE(ADDRESS2, NULL, 'EMPTY', ADDRESS2) FROM ADDRESSESSELECT CASE WHEN ADDRESS2 IS NULL THEN 'EMPTY' ELSE ADDRESS2 END FROM ADDRESSES
The next time you’re dealing with NULL values and you’d prefer a nicer looking result; consider giving the COALESCE function a try.
You seem to have a habit of finding the best Oracle goodies. – This should clean up the code rather nicely for a project I’m working on.
Thanks for the tip.
Yes the COALESCE function is handy for the “at least one of these things probably isn’t NULL” situations, but for your example
SELECT COALESCE(ADDRESS2, ‘EMPTY’) FROM ADDRESSES
surely the long standing Oracle NVL (null value) function is as good and a little more mnemonic?
SELECT NVL(ADDRESS2, ‘EMPTY’) FROM ADDRESSES
I’m hardly an Oracle head in long standing (about 5 years on and off) so, who am I to say what’s good or better :) I did appreciate being reminded about RETURNING (one of your other entries) though.
Best regards
How do you use the COALESCE function with DATE/TIME?
Tshepho,
You would use the
COALESCEfunction in Oracle the same for any data type. In your case (though it is applicable everywhere):COALESCE(datetime, exp1, val1, exp2, val2, val3)In the above example, if the value of the
datetimevariable/value is equal toexp1, thenval1would be substituted in. The same would hold forexp2/val2and if neitherexp1/exp2match – thenexp3would be substituted in.Hope this helps to clarify it a little further,
Al.