Diferencias de Oracle entre NVL y Coalesce

NVL hará una conversión implícita al tipo de datos del primer parámetro, por lo que lo siguiente no produce error

select nvl('a',sysdate) from dual;

COALESCE espera tipos de datos consistentes.

select coalesce('a',sysdate) from dual;

Lanzará un'error de tipo de datos inconsistente'

También hay diferencia en el manejo del plan.

Oracle puede formar un plan optimizado con concatenación de filtros de rama cuando la búsqueda contiene la comparación del resultado nvl con una columna indexada.

create table tt(a, b) as
select level, mod(level,10)
from dual
connect by level<=1e4;

alter table tt add constraint ix_tt_a primary key(a);
create index ix_tt_b on tt(b);

explain plan for
select * from tt
where a=nvl(:1,a)
  and b=:2;

explain plan for
select * from tt
where a=coalesce(:1,a)
  and b=:2;

Nvl:

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     2 |    52 |     2   (0)| 00:00:01 |
|   1 |  CONCATENATION                |         |       |       |            |          |
|*  2 |   FILTER                      |         |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_TT_B |     7 |       |     1   (0)| 00:00:01 |
|*  5 |   FILTER                      |         |       |       |            |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | IX_TT_A |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:1 IS NULL)
   3 - filter("A" IS NOT NULL)
   4 - access("B"=TO_NUMBER(:2))
   5 - filter(:1 IS NOT NULL)
   6 - filter("B"=TO_NUMBER(:2))
   7 - access("A"=:1)

Se unen:

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    26 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TT_B |    40 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=COALESCE(:1,"A"))
   2 - access("B"=TO_NUMBER(:2))

Los créditos van a http://www.xt-r.com/2012/03/nvl-coalesce-concatenation.html .

NVL y COALESCE se utilizan para lograr la misma funcionalidad de proporcionar un valor predeterminado en caso de que la columna devuelva un NULL.

Las diferencias son:

1. NVL acepta solo 2 argumentos mientras que COALESCE puede tomar múltiples argumentos
2. NVL evalúa tanto los argumentos como las paradas de COALESCE al principio ocurrencia de un valor no nulo.
3. NVL realiza una conversión de tipo de datos implícita basada en el primer argumento dado a ella. COALESCE espera que todos los argumentos sean de la misma tipo de datos.
4. COALESCE da problemas en las consultas que utilizan cláusulas de UNIÓN. Ejemplo abajo
5. COALESCE es un estándar ANSI donde as NVL es específico de Oracle.

Ejemplos para el tercer caso. Otros casos son simples.

select nvl('abc',10) from dual; funcionaría como NVL hará una conversión implícita de 10 numérico a cadena.

select coalesce('abc',10) from dual; fallará con tipos de datos inconsistentes de error: CHAR esperado número obtenido

Ejemplo de caso de uso de la UNIÓN

SELECT COALESCE(a, sysdate) 
from (select null as a from dual 
      union 
      select null as a from dual
      );

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

Tiene éxito.

Más información : http://www.plsqlinformation.com/2016/04/difference-between-nvl-and-coalesce-in-oracle.html

Otra prueba de que coalesce() no detiene la evaluación con el primer valor no nulo:

SELECT COALESCE(1, my_sequence.nextval) AS answer FROM dual;

Ejecute esto, luego verifique my_sequence.currval;

Aunque esta es obvia, e incluso mencionada de una manera planteada por Tom quien hizo esta pregunta. Pero vamos a poner de nuevo.

NVL puede tener solo 2 argumentos. Coalesce puede tener más de 2.

select nvl('','',1) from dual; //Resultado: ORA-00909: número incorrecto de argumentos
select coalesce('','','1') from dual; //Salida: devuelve 1

En realidad no puedo estar de acuerdo con cada declaración.

"COALESCE espera que todos los argumentos sean del mismo tipo de datos."

Esto está mal, ver más abajo. Los argumentos pueden ser diferentes tipos de datos, que también se documenta : Si todas las ocurrencias de expr son tipo de datos numéricos o cualquier tipo de datos no numéricos que se puede convertir implícitamente a un tipo de datos numéricos, Oracle Database determina el argumento con la precedencia numérica más alta, convierte implícitamente los argumentos restantes a ese tipo de datos, y devuelve ese tipo de datos.. En realidad, esto está incluso en contradicción con la expresión común "COALESCE se detiene en la primera aparición de un valor no nulo", de lo contrario, el caso de prueba No.4 no debería generar un error.

También de acuerdo con el caso de prueba No.5 COALESCE hace una conversión implícita de argumentos.

DECLARE
    int_val INTEGER := 1;
    string_val VARCHAR2(10) := 'foo';
BEGIN

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '1. NVL(int_val,string_val) -> '|| NVL(int_val,string_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('1. NVL(int_val,string_val) -> '||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '2. NVL(string_val, int_val) -> '|| NVL(string_val, int_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('2. NVL(string_val, int_val) -> '||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '3. COALESCE(int_val,string_val) -> '|| COALESCE(int_val,string_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('3. COALESCE(int_val,string_val) -> '||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '4. COALESCE(string_val, int_val) -> '|| COALESCE(string_val, int_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('4. COALESCE(string_val, int_val) -> '||SQLERRM ); 
    END;

    DBMS_OUTPUT.PUT_LINE( '5. COALESCE(SYSDATE,SYSTIMESTAMP) -> '|| COALESCE(SYSDATE,SYSTIMESTAMP) );

END;

Output:

1. NVL(int_val,string_val) -> ORA-06502: PL/SQL: numeric or value error: character to number conversion error
2. NVL(string_val, int_val) -> foo
3. COALESCE(int_val,string_val) -> 1
4. COALESCE(string_val, int_val) -> ORA-06502: PL/SQL: numeric or value error: character to number conversion error
5. COALESCE(SYSDATE,SYSTIMESTAMP) -> 2016-11-30 09:55:55.000000 +1:0 --> This is a TIMESTAMP value, not a DATE value!

NVL: Reemplaza el valor null por valor.

COALESCE: Devuelve la primera expresión no nula de la lista de expresiones.

Tabla: PRICE_LIST

+----------------+-----------+
| Purchase_Price | Min_Price |
+----------------+-----------+
| 10             | null      |
| 20             |           |
| 50             | 30        |
| 100            | 80        |
| null           | null      |
+----------------+-----------+   

A continuación se muestra el ejemplo de

[1] Establecer el precio de venta con la adición de 10% de beneficio a todos los productos.
[2] Si no hay precio de lista de compra, entonces el precio de venta es el precio mínimo. Para la venta de liquidación.
[3] Si no hay un precio mínimo también, a continuación, establecer el precio de venta como precio por defecto "50".

SELECT
     Purchase_Price,
     Min_Price,
     NVL(Purchase_Price + (Purchase_Price * 0.10), Min_Price)    AS NVL_Sales_Price,
COALESCE(Purchase_Price + (Purchase_Price * 0.10), Min_Price,50) AS Coalesce_Sales_Price
FROM 
Price_List

Explicar con ejemplo práctico de la vida real.

+----------------+-----------+-----------------+----------------------+
| Purchase_Price | Min_Price | NVL_Sales_Price | Coalesce_Sales_Price |
+----------------+-----------+-----------------+----------------------+
| 10             | null      | 11              |                   11 |
| null           | 20        | 20              |                   20 |
| 50             | 30        | 55              |                   55 |
| 100            | 80        | 110             |                  110 |
| null           | null      | null            |                   50 |
+----------------+-----------+-----------------+----------------------+

Puedes ver que con NVL podemos lograr reglas [1],[2]
Pero con COALSECE podemos lograr las tres reglas.