NVL vs. COALESCE

Lewis Cunningham just posted about timing differences between NVL and COALESCE. It reminded me of a very important difference that I didn’t know about until a couple years ago that can make the timing differences even greater.

NVL will evaluate both inputs, where COALESCE will stop as soon as it finds a non-null value. The case where I learned this was code that was passing 2 function calls as the inputs to NVL. I was surprised to learn at the time that both function calls were being run, even if the first returned a non-null value. COALESCE does not do this. If the first function returns a non-null value, the second function is never called.

Here is a quick example to show this effect:

DECLARE
   v_result VARCHAR2(10);

   FUNCTION dummy(v_in IN VARCHAR2)
   RETURN VARCHAR2
   IS
   BEGIN
       dbms_output.put_line('Called');
       RETURN v_in;
   END dummy;

BEGIN
    dbms_output.put_line('NVL, First null');
    v_result := nvl(dummy(null), dummy('b'));
    dbms_output.put_line('NVL, Both not null');
    v_result := nvl(dummy('a'), dummy('b'));

    dbms_output.put_line('COALESCE, First null');
    v_result := coalesce(dummy(null), dummy('b'));
    dbms_output.put_line('COALESCE, Both not null');
    v_result := coalesce(dummy('a'), dummy('b'));
END;

NVL, First null
Called
Called
NVL, Both not null
Called
Called
COALESCE, First null
Called
Called
COALESCE, Both not null
Called

You can see from the output that with NVL, the function is called both times, even if the first value returns a not null value. However, COALESCE does not call the 2nd function in the last case where the first value returns a not null value.

Of course, the documentation says as much. While it doesn’t make the NVL behavior explicitly clear, the COALESCE documentation does say:

Oracle Database uses short-circuit evaluation. That is, the database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.