The difference between COALESCE and NVL

Question

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 the solution:

Coalesce

Select COALESCE(A,B) Teste
 from TabelaTeste

NVL

Select NVL(A,B) Teste
     from TabelaTeste

What is the difference between these commands? Are any more performative? What criteria should I use to choose?

Answer 1

The function NVL is specific to Oracle and only accepts two expressions as input.

If the first expression is null, the function will return the second expression. Otherwise, the first expression will be returned.

Input expressions can also be of different types, if this happens, an attempt will be made to cast implicit, if the cast is not possible an error will be returned.

Also, this function always evaluates the two input expressions, making it slightly less performative than the COALESCE.

Illustration of function NVL:

.

^{Documentation image}

The COALESCE is part of the pattern ANSI-92, therefore 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 need to specify at least two expressions, but you can specify more.

Illustration of function COALESCE:

^{Documentation image}

Answer 2

The differences are:

1. COALESCE follows the ANSI standard while NVL is specific to Oracle;
2. NVL accepts only 2 pointers while COALESCE can receive multiple arguments;
3. NVL executes both arguments and COALESCE for the first occurrence of a non-zero value;
4. NVL makes an implicit conversion of the data type based on the first parameter reported, COALESCE expects all parameters to be of the same type;

Example:

select nvl('abc',10) from dual; It will work with 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".

5. COALESCE has problems in queries using UNION clauses (Example below);

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
      ) ;

Will successfully execute.

Source: Stack Overflow