Oracle Различия между NVL и Coalesce

COALESCE - это более современная функция, которая является частью стандарта ANSI-92.

NVL специфичен для Oracle, он был введен в 80 до появления каких-либо стандартов.

В случае двух значений они являются синонимами.

Однако реализуются они по-разному.

NVL всегда оценивает оба аргумента, а COALESCE обычно останавливает оценку всякий раз, когда находит первый не-NULL (есть некоторые исключения, такие как последовательность NEXTVAL):

SELECT  SUM(val)
FROM    (
        SELECT  NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

Это длится почти 0.5 секунд, поскольку генерирует SYS_GUID(), несмотря на то, что 1 не является NULL.

SELECT  SUM(val)
FROM    (
        SELECT  COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

Это понимает, что 1 не NULL, и не оценивает второй аргумент.

SYS_GUID не генерируются, и запрос выполняется мгновенно.

NVL выполнит неявное преобразование в тип данных первого параметра, поэтому следующее не приведет к ошибке

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

COALESCE ожидает согласованных типов данных.

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

выдаст 'ошибку несогласованного типа данных'

NVL и COALESCE используются для достижения той же функциональности по предоставлению значения по умолчанию в случае, если столбец возвращает NULL.

Отличия заключаются в следующем:

1. NVL принимает только 2 аргумента, тогда как COALESCE может принимать несколько аргументов.
2. NVL оценивает оба аргумента, и COALESCE останавливается при первом появлении ненулевого значения.
3. NVL выполняет неявное преобразование типа данных на основе первого переданного ему аргумента. COALESCE ожидает, что все аргументы будут одного типа данных.
4. COALESCE выдает проблемы в запросах, в которых используются предложения UNION. Пример ниже
5. COALESCE - это стандарт ANSI, а NVL - специфичный для Oracle.

Примеры для третьего случая. Остальные случаи просты.

select nvl('abc',10) from dual; будет работать, поскольку NVL будет выполнять неявное преобразование числа 10 в строку.

select coalesce('abc',10) from dual; завершится ошибкой - несовместимые типы данных: ожидаемый CHAR получил ЧИСЛО

Пример сценария использования UNION

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

не работает с 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
      ) ;

удается.

Дополнительная информация: http://www.plsqlinformation.com/2016/04/difference-between-nvl-and-coalesce-in-oracle.html

Также есть разница в обработке планов.

Oracle может формировать оптимизированный план с объединением фильтров ветвей, когда поиск содержит сравнение nvl результата с индексированным столбцом.

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)

слиться:

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

Кредиты переходят на http://www.xt-r.com/2012/03/nvl-coalesce-concatenation.html.

Еще одно доказательство того, что coalesce () не останавливает вычисление с первым ненулевым значением:

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

Запустите это, затем проверьте my_sequence.currval;

NVL: замените ноль значением.

COALESCE: возвращает первое ненулевое выражение из списка выражений.

Таблица: PRICE_LIST

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

Ниже приведен пример

[1] Установить продажную цену с добавлением 10% прибыли ко всем продуктам.
[2] Если прейскурантной цены закупки нет, то продажная цена является минимальной. цена. Для распродажи.
[3] Если минимальной цены также нет, установите цену продажи по умолчанию «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

Объясните на практическом примере из жизни.

+----------------+-----------+-----------------+----------------------+
| 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 |
+----------------+-----------+-----------------+----------------------+

Вы можете видеть, что с помощью NVL мы можем достичь правил [1], [2]
Но с COALSECE мы можем достичь всех трех правил.

На самом деле я не могу согласиться с каждым утверждением.

«COALESCE ожидает, что все аргументы будут одного типа данных».

Это неправильно, см. Ниже. Аргументы могут быть разными типами данных, что также задокументировано: < em> Если все вхождения expr являются числовым типом данных или любым нечисловым типом данных, который может быть неявно преобразован в числовой тип данных, то Oracle Database определяет аргумент с наивысшим числовым приоритетом, неявно преобразует остальные аргументы в этот тип данных и возвращает этот тип данных.. На самом деле это даже противоречит общему выражению «COALESCE останавливается при первом появлении ненулевого значения», иначе тестовый пример № 4 не должен вызывать ошибку.

Также согласно контрольному примеру № 5 COALESCE выполняет неявное преобразование аргументов.

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 может иметь только 2 аргумента. Coalesce может иметь более 2.

select nvl('','',1) from dual; // Результат: ORA-00909: недопустимое количество аргументов
select coalesce('','','1') from dual; // Вывод: возвращает 1