SQL NVL 和 Coalesce 之间的 Oracle 差异

Oracle Differences between NVL and Coalesce

提问by Tom Hubbard

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?

我错过了什么吗？

回答by Quassnoi

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

COALESCE更现代的功能是ANSI-92标准的一部分。

NVLis Oraclespecific, 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.

但是，它们的实现方式不同。

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

NVL始终评估两个参数，而COALESCE通常在找到第一个非 - 时停止评估NULL（有一些例外，例如 sequence 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.5seconds, since it generates SYS_GUID()'s, despite 1being 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 1is not a NULLand does not evaluate the second argument.

这理解1不是 aNULL并且不评估第二个参数。

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

SYS_GUID不会生成并且查询是即时的。

回答by Gary Myers

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.

COALESCE 需要一致的数据类型。

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

will throw a 'inconsistent datatype error'

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

回答by Brahmareddy K

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:

区别在于：

1. NVL accepts only 2 arguments whereas COALESCE can take multiple arguments
2. NVL evaluates both the arguments and COALESCE stops at first occurrence of a non-Null value.
3. NVL does a implicit datatype conversion based on the first argument given to it. COALESCE expects all arguments to be of same datatype.
4. COALESCE gives issues in queries which use UNION clauses. Example below
5. COALESCE is ANSI standard where as NVL is Oracle specific.

1. NVL 只接受 2 个参数，而 COALESCE 可以接受多个参数
2. NVL 评估参数，并且 COALESCE 在第一次出现非 Null 值时停止。
3. NVL 根据提供给它的第一个参数进行隐式数据类型转换。COALESCE 期望所有参数都具有相同的数据类型。
4. COALESCE 在使用 UNION 子句的查询中给出问题。下面的例子
5. COALESCE 是 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.

select nvl('abc',10) from dual;会起作用，因为 NVL 会将数字 10 隐式转换为字符串。

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

select coalesce('abc',10) from dual;将因错误而失败 - 不一致的数据类型：预期的 CHAR 得到 NUMBER

Example for UNION use-case

UNION 用例示例

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: inconsistent datatypes: expected CHAR got DATE

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

回答by Vadzim

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 nvlresult 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:

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。

回答by Herb Swift

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

另一个证明 coalesce() 不会停止对第一个非空值进行评估的证据：

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

Run this, then check my_sequence.currval;

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

回答by Wernfried Domscheit

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 数据库将确定具有最高数字优先级的参数，隐式将剩余的参数转换为该数据类型，并返回该数据类型。. 实际上这甚至与常见的表达“COALESCE 在第一次出现非空值时停止”相矛盾，否则测试用例 4 不应引发错误。

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

同样根据测试用例 5COALESCE进行了参数的隐式转换。

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!

回答by sandip

NVL:Replace the null with value.

NVL：用值替换空值。

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

COALESCE：从表达式列表中返回第一个非空表达式。

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]
但是使用 COALSECE 我们可以实现所有三个规则。

回答by Neel

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 个参数。Coalesce 可能有 2 个以上。

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

select nvl('','',1) from dual;//结果：：ORA-00909无效的参数数量
select coalesce('','','1') from dual;//输出：返回 1