Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

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?

1 Answer

0 votes
by (40.7k points)

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.

Related questions

Browse Categories

...