COALESCE function

I have been using NVL.  However, I think that we should all move to use 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.

This testing describes the reason:



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.

 COALESCE is much efficient than NVL!