Using COALESCE Function in PLSQL Query

https://plsqlquery.blogspot.com/2018/05/using-coalesce-function-in-plsql-query.html

The Oracle/PLSQL COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null.

COALESCE( expr1, expr2, ... expr_n )

Returns
The COALESCE function returns returns any datatype such as a string, numeric, date, etc. (BUT all expressions must be the same datatype in the COALESCE function.)
If all expressions are not the same datatype, an ORA-00932 error will be returned.

The above COALESCE function is equivalent to the following IF-THEN-ELSE statement:

IF address1 is not null THEN
   result := address1;

ELSIF address2 is not null THEN
   result := address2;

ELSIF address3 is not null THEN
   result := address3;

ELSE
   result := null;

END IF;

The COALESCE function will compare each value, one by one.

Select ENAME, COMM, SAL, COALESCE(COMM,SAL,100) SALARY from EMP
where ENAME in ('KING', 'WARD', 'TURNER');

ENAME       COMM        SAL          SALARY
KING               (null)           5000          5000
WARD              500            1250          500
TURNER           0               1500          0


select ENAME, COMM, NVL(COMM,0) , COALESCE(COMM,0), COALESCE(COMM, SAL,0)
from EMP where ENAME in ('KING', 'WARD', 'TURNER');

ENAME    COMM   NVL(COMM,0)    COALESCE(COMM,0)   COALESCE(COMM, SAL,0)
KING          (null)            0                               0                                        5000
WARD         500             500                          500                                     500
TURNER      0                0                               0                                         0