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:
- NVL accepts only 2 arguments whereas COALESCE can take multiple arguments
- NVL evaluates both the arguments and COALESCE stops at first occurrence of a non-Null value.
- NVL does a implicit datatype conversion based on the first argument given to it. COALESCE expects all arguments to be of same datatype.
- COALESCE gives issues in queries which use UNION clauses. Example below
- COALESCE is ANSI standard where as NVL is Oracle specific.
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 );
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 ) ;