Oracle Differences between NVL and Coalesce - Stack Overflow
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 stops evaluation whenever it finds first non-NULL:
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.
9
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'
