Oracle Differences between NVL and Coalesce

COALESCE is more modern function that is a part of ANSI-92 standard.

NVL is Oracle specific, it was introduced in 80's before there were any standards.

In case of two values, they are synonyms.

However, they are implemented differently.

NVL always evaluates both arguments, while COALESCE usually stops evaluation whenever it finds the first non-NULL (there are some exceptions, such as sequence NEXTVAL):

SELECT  SUM(val)FROM    (        SELECT  NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val        FROM    dual        CONNECT BY                level <= 10000        )

This runs for almost 0.5 seconds, since it generates SYS_GUID()'s, despite 1 being not a NULL.

SELECT  SUM(val)FROM    (        SELECT  COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val        FROM    dual        CONNECT BY                level <= 10000        )

This understands that 1 is not a NULL and does not evaluate the second argument.

SYS_GUID's are not generated and the query is instant.

NVL will do an implicit conversion to the datatype of the first parameter, so the following does not error

select nvl('a',sysdate) from dual;

COALESCE expects consistent datatypes.

select coalesce('a',sysdate) from dual;

will throw a 'inconsistent datatype error'

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 multiplearguments
2. NVL evaluates both the arguments and COALESCE stops at firstoccurrence of a non-Null value.
3. NVL does a implicit datatype conversion based on the first argumentgiven to it. COALESCE expects all arguments to be of same datatype.
4. COALESCE gives issues in queries which use UNION clauses. Examplebelow
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.

More information : http://www.plsqlinformation.com/2016/04/difference-between-nvl-and-coalesce-in-oracle.html