NVL vs. COALESCE

http://explainextended.com/2009/06/20/nvl-vs-coalesce/


Coalesce is just like NVL in the sense that they both return the first argument in the list that is not null, or null if all arguments are null.

Having said that, there are differences.
NVL takes 2 arguments, coalesce 2 or more.
NVL(a,b) will return b if a is null
COALESCE(a,b) will do the same
COALESCE(a,b,c,d) will return b if a is null, c if a and b are null, d if a, b and c are null., null if all are null


And, in my opinion, more important:
NVL evaluates BOTH arguments, Coalesce stops as soon as an argument is found that is not null

So, suppose the situation where a is NOT null, then
NVL(a, my_function_call) and
COALESCE(a, my_function_call)
will both return a.
But when using NVL my_function_call will nevertheless be executed, although it's returned value will obviously not be used.
Coalesce will stop at a because it is not null, and so, my_function_call will not be executed.
So, NVL could have unwanted side effects, such as performance problems because of unnecessary executions of functions or unwanted DML (within the function, bad idea), or unnecessary calls to sequences (if the function returns a nextval) leaving holes in you id ranges.

Also Coalesce will not auto convert data type but NVL does.

following statement goes fine for NVL.

select NVL('2',1) from dual;

But fails for Coalesce

select coalesce('2',1) from dual;

following can be used to check if it is evaluating 2nd parameter  or not if  first parameter is not null. 

Note: Coalesce does not execute function but to validate return type it validates status of object, hence if object has compilation issues it will return error.

drop table id12;

create table id12 (id number);

insert into id12 values(1);
insert into id12 values(2);

insert into id12 values(3);

commit;

CREATE OR REPLACE FUNCTION funct
    RETURN number
IS
BEGIN
    DBMS_OUTPUT.put_line ( 'Executed');
    RETURN 10;
END;
/

set serveroutput on ;

select nvl(id,funct) from id12;


select coalesce(id,funct, 1,funct) from id12;

Example #2: You will notice that with each execution of NVL sequence value is incrementing but not with coalesce.

create sequence a_seq;


create or replace function seq_funct return  number
as
a number;
begin
a:=a_seq.nextval;
return a;
end;
/

select coalesce(1,seq_funct) from dual

select nvl(1,seq_funct) from dual


select a_seq.currval from dual