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结果与索引列的比较时，Oracle可以通过串联分支过滤器形成优化计划。

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。

虽然这一点很明显，甚至在汤姆提出这个问题时也提到了。但让我们再放一遍。

NVL只能有2个参数。Coalesce可能有2个以上。

Select nvl("， "，1) from dual;//Result: ORA-00909: invalid number of arguments .(无效参数个数。 从dual中选择coalesce("， "，'1');//返回1

NVL和COALESCE用于实现相同的功能，即在列返回NULL时提供默认值。

区别在于:

NVL只接受2个参数，而COALESCE可以接受多个参数 参数 NVL计算两个参数，COALESCE首先停止 非空值的出现。 NVL基于第一个参数进行隐式数据类型转换 给它。COALESCE期望所有参数都具有相同的数据类型。 COALESCE在使用UNION子句的查询中给出问题。例子 下面 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失败:不一致的数据类型:预期的CHAR得到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

NVL:将null替换为value。

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期望所有参数具有相同的数据类型。

This is wrong, see below. Arguments can be different data types, that is also documented: If all occurrences of expr are numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.. Actually this is even in contradiction to common expression "COALESCE stops at first occurrence of a non-Null value", otherwise test case No. 4 should not raise an error.

根据测试用例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!

另一个coalesce()不停止对第一个非空值求值的证明:

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

运行这个，然后检查my_sequence.currval;