What is the difference between NVL and COALESCE?

Dear Patrick,

Could you tell me what the difference is between NVL and COALESCE?

Kindest regards,
Paul McCurdey

Dear Paul,

NVL returns the value of the first argument if it doesn’t evaluate to NULL, otherwise it will return the value of the second argument.
COALESCE returns the first argument that doesn’t evaluate to NULL. That can be any one of the arguments.
So they are definitely similar – but there are significant differences.
First of all, COALESCE is a function that’s part of the ANSI-92 standard whereas NVL was made in the 80′s when there were no standards. Since COALESCE is the newer function of the two (since 9i), it is better equipped for multiple values and it does less work, therefore it’s the greener option (doing less work means using less resources, like power, and therefore it is greener).
How is doing less work better, you might ask? Well, would you want to do the work for which you know the result is never going to be used? I know I wouldn’t. That is one thing COALESCE does for you. It does not evaluate an argument if its result is not needed.
A big advantage of using the COALESCE function is the short-circuit evaluation. Where NVL evaluates both arguments, whether the second argument should be used or not, COALESCE only evaluates the arguments if they are needed.
For example:
If you run the following statement:

[PATRICK]SQL>SELECT NVL(1, 1/0) FROM dual
             /

you will see the

ORA-01476: division by zero

error.
In this statement the first argument (1) is NOT NULL so the second argument (1/0) should not be returned, but since with NVL PL/SQL evaluates the expression, this statement results in the exception.
The COALESCE function only evaluates the arguments if they are needed.
If you run the following statement:

[PATRICK]SQL>SELECT COALESCE(1, 1/0) FROM dual
          2  /

you will not get an error, since the second argument is not evaluated. In other words it evaluates exprN only if expr(N-1) evaluates to NULL.

A simple test shows the difference again:
First we create simple package which holds a variable and a function to increase that variable:

[PATRICK]SQL>CREATE OR REPLACE PACKAGE nvl_test
             IS
               g_value NUMBER := 0;
               FUNCTION increase_value RETURN NUMBER;
             END nvl_test;
             /
[PATRICK]SQL>CREATE OR REPLACE PACKAGE BODY nvl_test
             IS
               FUNCTION increase_value RETURN NUMBER
               IS
                l_returnvalue NUMBER;
               BEGIN
                 dbms_output.put_line('nvl_test.increase_value');
                 nvl_test.g_value := nvl_test.g_value + 1;
                 l_returnvalue := nvl_test.g_value;
                 RETURN l_returnvalue;
               END increase_value;
             END nvl_test;
             /

Then a script to demonstrate what happens. First display the value of the variable. Then call the NVL function where the first value is NULL. As you can see, the function in the package is called, hence the variable is increased. Then another call to the NVL function, this time with a non NULL value. The function in the package is still called even though its value is not being used.
Then we reset the value of the variable and run the same tests, but this time using the COALESCE function. As you can see, the function is only being called if the previous argument(s) evaluate to NULL.

[PATRICK]SQL>DECLARE
               l_dummy NUMBER;
               l_foo NUMBER;
             BEGIN
               dbms_output.put_line('====reset package====');
               nvl_test.g_value := 0;
               l_dummy := nvl_test.g_value;
               dbms_output.put_line(l_dummy);
               l_foo := NVL(NULL,nvl_test.increase_value);
               dbms_output.put_line(l_foo);
               l_dummy := nvl_test.g_value;
               dbms_output.put_line(l_dummy);
               l_foo := NVL(2912,nvl_test.increase_value);
               dbms_output.put_line(l_foo);
               l_dummy := nvl_test.g_value;
               dbms_output.put_line(l_dummy);
               dbms_output.put_line('====reset package====');
               nvl_test.g_value := 0;
               l_dummy := nvl_test.g_value;
               dbms_output.put_line(l_dummy);
               l_foo := coalesce(NULL,nvl_test.increase_value);
               dbms_output.put_line(l_foo);
               l_dummy := nvl_test.g_value;
               dbms_output.put_line(l_dummy);
               l_foo := coalesce(2912,nvl_test.increase_value);
               dbms_output.put_line(l_foo);
               l_dummy := nvl_test.g_value;
               dbms_output.put_line(l_dummy);
             END;
             /

====reset package====
0
nvl_test.increase_value
1
1
nvl_test.increase_value
2912
2
====reset package====
0
nvl_test.increase_value
1
1
2912
1

If you run the anonymous block in an IDE where you can step through the code, you can see when the code is executed and when it is bypassed.
So, if you don’t need or want the code executed when the value of a variable or result of a function is not NULL, then you should use COALESCE to prevent this from happening. But there might be a use case in which you always want a piece of code executed whether the first argument is NULL or not. Then you should use (or stick to) NVL.
I think the rule should be: Use COALESCE unless…

Hope this answers your question.
Happy Oracle’ing,
Patrick Barel

If you have a question you want answered, please send an email to patrick[at]bar-solutions[dot]com. If I know the answer, or can find it for you, maybe I can help.

This question has been published in OTech Magazine of Fall 2014