NVL和Coalesce之间的Oracle差异

Are there non obvious differences between NVL and Coalesce in Oracle?

Oracle中NVL和Coalesce之间是否存在不明显的差异?

The obvious differences are that coalesce will return the first non null item in its parameter list whereas nvl only takes two parameters and returns the first if it is not null, otherwise it returns the second.

明显的区别是，coalesce将返回其参数列表中的第一个非空项，而nvl只接受两个参数，如果第一个参数不是空的，则返回第一个，否则返回第二个。

It seems that NVL may just be a 'Base Case" version of coalesce.

看起来NVL可能只是一个“基本情况”版本的联合。

Am I missing something?

我遗漏了什么东西?

8 个解决方案

#1

261

COALESCE is more modern function that is a part of ANSI-92 standard.

联合是ANSI-92标准的一个更现代的功能。

NVL is Oracle specific, it was introduced in 80's before there were any standards.

NVL是Oracle特有的，它是在80年代引入的，那时还没有任何标准。

In case of two values, they are synonyms.

对于两个值，它们是同义词。

However, they are implemented differently.

然而，它们的实现是不同的。

NVL always evaluates both arguments, while COALESCE usually stops evaluation whenever it finds the first non-NULL (there are some exceptions, such as sequence NEXTVAL):

NVL总是对两个参数进行求值，而联合ce通常在找到第一个非空值时停止求值(有一些例外，如序列NEXTVAL):

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

This runs for almost 0.5 seconds, since it generates SYS_GUID()'s, despite 1 being not a NULL.

这将运行近0.5秒，因为它生成SYS_GUID()的s，尽管1不是NULL。

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

This understands that 1 is not a NULL and does not evaluate the second argument.

它知道1不是NULL，也不计算第二个参数。

SYS_GUID's are not generated and the query is instant.

SYS_GUID不是生成的，查询是即时的。

#2

155

NVL will do an implicit conversion to the datatype of the first parameter, so the following does not error

NVL将对第一个参数的数据类型进行隐式转换，因此以下内容不会出错

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

COALESCE expects consistent datatypes.

合并预计一致的数据类型。

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

will throw a 'inconsistent datatype error'

将抛出一个“不一致的数据类型错误”

#3

There is also difference is in plan handling.

在计划处理上也有差异。

Oracle is able form an optimized plan with concatenation of branch filters when search contains comparison of nvl result with an indexed column.

当搜索包含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)

coalesce:

合并:

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

Credits go to http://www.xt-r.com/2012/03/nvl-coalesce-concatenation.html.

学分去http://www.xt-r.com/2012/03/nvl-coalesce-concatenation.html。

#4

NVL and COALESCE are used to achieve the same functionality of providing a default value in case the column returns a NULL.

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

The differences are:

的差异是:

NVL accepts only 2 arguments whereas COALESCE can take multiple arguments
NVL只接受两个参数，而联合可以接受多个参数
NVL evaluates both the arguments and COALESCE stops at first occurrence of a non-Null value.
NVL对参数进行评估，并在首次出现非空值时停止合并。
NVL does a implicit datatype conversion based on the first argument given to it. COALESCE expects all arguments to be of same datatype.
NVL根据给出的第一个参数进行隐式数据类型转换。COALESCE期望所有参数都具有相同的数据类型。
COALESCE gives issues in queries which use UNION clauses. Example below
联合在使用联合子句的查询中给出问题。在下面的例子
COALESCE is ANSI standard where as NVL is Oracle specific.
联合是ANSI标准，其中NVL是Oracle特有的。

Examples for the third case. Other cases are simple.

第三种情况的例子。其他情况下很简单。

select nvl('abc',10) from dual; would work as NVL will do an implicit conversion of numeric 10 to string.

选择nvl从双(“abc”,10);当NVL进行数值10到字符串的隐式转换时，就可以工作了。

select coalesce('abc',10) from dual; will fail with Error - inconsistent datatypes: expected CHAR got NUMBER

选择合并从双(“abc”,10);将失败与错误不一致的数据类型:期望字符得到的数字

Example for UNION use-case

联盟的示例用例

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

fails with ORA-00932: inconsistent datatypes: expected CHAR got DATE

ORA-00932:不一致的数据类型:预期的CHAR获得日期

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

succeeds.

成功。

More information : http://www.plsqlinformation.com/2016/04/difference-between-nvl-and-coalesce-in-oracle.html

更多信息:http://www.plsqlinformation.com/2016/04/difference-between-nvl-and-coalesce-in-oracle.html。

#5

Though this one is obvious, and even mentioned in a way put up by Tom who asked this question. But lets put up again.

虽然这一点是显而易见的，甚至以汤姆提出这个问题的方式被提及。但是让我们再忍受一次。

NVL can have only 2 arguments. Coalesce may have more than 2.

NVL只能有两个参数。合并可能大于2。

select nvl('','',1) from dual; //Result: ORA-00909: invalid number of arguments
select coalesce('','','1') from dual; //Output: returns 1

选择nvl从双("、",1);//结果:ORA-00909:无效的参数数从dual中选择coalesce(“，”，“1”);/ /输出:返回1

#6

Actually I cannot agree to each statement.

实际上我不同意每个说法。

"COALESCE expects all arguments to be of same datatype."

“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.

这是错误的，请看下面。参数可以是不同的数据类型,这也是记录:如果出现的所有expr数值数据类型或任何非数字数据类型,可以隐式转换为数值数据类型,那么Oracle数据库确定参数数值最高的优先级,隐式转换,其余参数数据类型,并返回数据类型。实际上，这甚至与常见的表达式“在非空值首次出现时停止合并”相矛盾，否则测试用例4不应引发错误。

Also according to test case No. 5 COALESCE does an implicit conversion of arguments.

另外，根据测试用例第5号合并会对参数进行隐式转换。

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!

#7

Another proof that coalesce() does not stop evaluation with the first non-null value:

合并()的另一个证明不停止对第一个非空值的评估:

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

Run this, then check my_sequence.currval;

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

#8

NVL: Replace the null with value.

NVL:用值替换null。

COALESCE: Return the first non-null expression from expression list.

合并:返回表达式列表中的第一个非空表达式。

Table: PRICE_LIST

表:PRICE_LIST

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

Below is the example of

[1] Set sales price with adding 10% profit to all products.
[2] If there is no purchase list price, then the sale price is the minimum price. For clearance sale.
[3] If there is no minimum price also, then set the sale price as default price "50".

下面是[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

Explain with real life practical example.

用现实生活中的实际例子来解释。

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

You can see that with NVL we can achieve rules [1],[2]
But with COALSECE we can achieve all three rules.

可以看到，有了NVL，我们就可以实现规则[1]，[2]但是有了合并，我们就可以实现这三个规则。

#1

261