Oracle COALESCE Function

Database, Programming

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:

  1. COALESCE(arg1, arg2, ..., argN)

There are many practical uses for the Oracle COALESCE function, two common actions include:

  1. 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.
  2. 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:

  1. 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:

  1. SELECT DECODE(ADDRESS2, NULL, 'EMPTY', ADDRESS2) FROM ADDRESSES
  2. 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.

Related posts:

  1. Oracle Dynamic SQL Using The DECODE Function
  2. Oracle DECODE Function
  3. Storing Time Information In Oracle
  4. Oracle RETURNING Clause
  5. Oracle Derived Columns In SELECT Statements

4 thoughts on “Oracle COALESCE Function”

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

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

  4. Tshepho,

    You would use the COALESCE function in Oracle the same for any data type. In your case (though it is applicable everywhere):

    1. COALESCE(datetime, exp1, val1, exp2, val2, val3)

    In the above example, if the value of the datetime variable/value is equal to exp1, then val1 would be substituted in. The same would hold for exp2/val2 and if neither exp1/exp2 match – then exp3 would be substituted in.

    Hope this helps to clarify it a little further,
    Al.

Comments are closed.