In Oracle when we have a table that has two campuses A and B, and in selection ( select ) we want to show B when A is null we have two commands that present us with the solution:
Coalesce
Select COALESCE(A,B) Teste
from TabelaTeste
NVL
Select NVL(A,B) Teste
from TabelaTeste
What's the difference between these commands? Is it more performative? What criteria should I use to choose?
The function NVL is and accepts only two expressions as input.
If the first expression is null, the function returns the second expression. Otherwise, the first expression is returned.
Input expressions can also be of different types, if this happens, an implicit cast attempt will be made, if cast is not possible an error will be returned .
In addition, this function always evaluates the two input expressions, making it slightly less performative than COALESCE .
Illustration of the function NVL :
.
Documentation Image
COALESCE is part of the ANSI-92 standard, so it is a command that exists in all databases that follow this pattern or higher.
It always returns the first non-null value in the expression list. You must specify at least two expressions, but you can specify more.
Illustration of the function COALESCE :
Documentation Image
The differences are:
Example:
select nvl('abc',10) from dual; Will work with the implicit conversion from number 10 to string.
select coalesce('abc',10) from dual; It will generate the following error: "Inconsistent datatypes: expected CHAR got NUMBER".
Example:
SELECT COALESCE(a, sysdate)
from (select null as a from dual
union
select null as a from dual
);
It will give the following error: "ORA-00932: inconsistent datatypes: expected CHAR got DATE".
SELECT NVL(a, sysdate)
from (select null as a from dual
union
select null as a from dual
) ;
It will run successfully.
Source: Stack Overflow