NVL and COALESCE

http://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce

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:

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

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.