NVL and COALESCE are used to achieve the same functionality of providing a default value in case the column returns a NULL.
The differences are:
Examples for the third case. Other cases are simple.
select nvl('abc',10) from dual;
would work as NVL will do an implicit conversion of numeric 10 to string.
select coalesce('abc',10) from dual;
will fail with Error - inconsistent datatypes: expected CHAR got NUMBER
Example for UNION use-case
SELECT COALESCE(a, sysdate)
from (select null as a from dual
union
select null as a from dual
);
fails with ORA-00932: inconsistent datatypes: expected CHAR got DATE
SELECT NVL(a, sysdate)
from (select null as a from dual
union
select null as a from dual
) ;
succeeds.
More information : http://www.plsqlinformation.com/2016/04/difference-between-nvl-and-coalesce-in-oracle.html