COALESCE is one of many convenient ways provided by oracle to handle NULL values in the database. The Oracle COALESCE function accepts a varying length list of arguments and returns the first non-NULL value/expr in the list. If all arguments in the list evaluate to a NULL value, then the COALESCE function will return a NULL value. Instead of scanning through all the arguments, oracle takes a short-circuit evaluation approach to processing what’s provided. In other words, it evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL. As soon as it find the first non-NULL value it will stop processing rest of the provided arguments.

There is always more than one way to achieve the same outcome. You can use COALESCE as a variety of the CASE or DECODE expression.

For example,
Using DECODE, you would do something like

SELECT DECODE(mgr, NULL, 9999, mgr) FROM SCOTT.EMP

OR

Using CASE, you would do something like

SELECT
CASE
WHEN MGR IS NULL THEN
9999
ELSE
MGR
END
FROM SCOTT.EMP;

The following demonstrates how to substitute in a default value in a SELECT statement when there are NULL values present using COALESCE function.

SELECT COALESCE(mgr, 9999) FROM SCOTT.EMP;

The statement above looks much cleaner, well at least to everyone who knows what’s going on. In examples above, 9999 is a default value that you would like to assign to any null values in the list.

But a better use of COALESCE would be when more than 1 value is passed to it as input.\
For example,

SQL> select coalesce(null,null, 5, null) from dual;

SQL>

As you can notice COALESCE picks up the first non-null value from a given list.

hope this helps.
Kirtan