Oracle Differences between NVL and Coalesce

coalesce, nvl, oracle, sql

Solution

`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.

Problem

Are there non obvious differences between NVL and Coalesce in Oracle? The obvious differences are that coalesce will return the first non null item in its parameter list whereas nvl only takes two parameters and returns the first if it is not null, otherwise it returns the second. It seems that NVL may just be a 'Base Case" version of coalesce. Am I missing something?

Original source