Oracle Differences between NVL and Coalesce

Question

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?

Answer 1

NVL is Oracle-specific, it had been introduced in the '80s before there have been any standards. Whereas, COALESCE is the modern function which is the part of the ANSI-92 standard. In the case of 2 values, they're synonyms. However, they're implemented in a different way.

NVL invariably evaluates each argument, whereas COALESCE sometimes stops analysis whenever it finds the primary non-NULL (their square measure some exceptions, like sequence NEXTVAL):

SELECT  SUM(val)

FROM    (

        SELECT  NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val

        FROM    dual

        CONNECT BY

                level <= 10000

        )

NVL runs for pretty much zero.5 seconds, since it generates SYS_GUID()'s, despite one being not a NULL.

SELECT  SUM(val)

FROM    (

        SELECT  COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val

        FROM    dual

        CONNECT BY

                level <= 10000

        )

Whereas, COALESCE understands that one isn't a NULL and doesn't assess the second argument.SYS_GUID's aren't generated and therefore the question is instant.

Answer 2

While comparing the ‘COALESCE’ and ‘NVL’ functions in Oracle, there are multiple differences beyond the basic functionality of NULL values.

Some Key Differences are: 

Functionality and Parameters 

Number of Arguments:

• ‘NVL’ accepts exactly two arguments and it returns the first argument if it is not NULL otherwise, it will return the second argument.

• ’COALESCE’ can handle multiple arguments (more than two) and it returns the first non-NULL value from the list of arguments.

Evaluation of Arguments:

• ‘NVL’ evaluates both arguments regardless of whether the first is NULL or not. It means that even if the second argument is a complex one, In that case, it will always be executed.

• ‘COALESCE’, uses short-circuit evaluation. It only evaluates the arguments until it finds the first non-NULL value. In some cases, this may lead to performance improvements.

Data Type handling:

• Implicit Conversion:

  • ‘NVL’ performs implicit conversion to match the data types of the arguments. You can mix data types without error

  • ‘COALESCE’ requires all the arguments to have consistent data types and if they mismatch, then it will throw an error

Examples

Example of NVL

SELECT NVL(NULL, 'Default Value') AS result FROM dual;

In this example, since the first argument is NULL, the result will be 'Default Value'.

Example of COALESCE

SELECT COALESCE(NULL, NULL, 'First Non-NULL', 'Another Value') AS result FROM dual;

In this case, the result will be 'First Non-NULL', as it is the first non-NULL value in the list.

Answer 3

NVL: No matter if the first argument is NULL or not, NVL will always evaluate both arguments. This can cause unnecessary computation if the second argument is heavy

SELECT SUM(total)

FROM (

    SELECT NVL(5, LENGTH(RAWTOHEX(SYS_GUID()))) AS total

    FROM dual

    CONNECT BY level <= 10000

)

In this case, the first argument is 5, which is non-NULL. However, this means NVL will try to evaluate the second one as well, which is, in this case, a rather heavy-weight operation (LENGTH of RAWTOHEX of SYS_GUID()). The execution can take up to 0.5 seconds because it generates the SYS_GUID() for each row, which in normal situations might not be necessary. When some of the NVL arguments are NULL, they do not generate unnecessary computations, which is a correct decision from an optimization perspective.

COALESCE: On the other hand, COALESCE will stop evaluations immediately once it sees the first non-null argument. Such techniques of lazy evaluation can provide much better performance. For example:

SELECT SUM(total)

FROM (

    SELECT COALESCE(5, LENGTH(RAWTOHEX(SYS_GUID()))) AS total

    FROM dual

    CONNECT BY level <= 10000

)

Since 5 is the first argument and it is non-null, COALESCE will return 5 and never call the second argument. As a result, the SQL query only generates 1 row, resolving almost instantly without causing any SYS_GUID() to be generated.

Use Case: For cases where it is necessary to replace a NULL value with a predetermined value and only two alternative values exist, NVL is appropriate. The disadvantage, however, is that both arguments are evaluated regardless of the value of the first argument.