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
NULL
values.
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 ADDRESSES
SELECT 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
COALESCE
function 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
datetime
variable/value is equal toexp1
, thenval1
would be substituted in. The same would hold forexp2
/val2
and if neitherexp1
/exp2
match – thenexp3
would be substituted in.Hope this helps to clarify it a little further,
Al.