NVL和Coalesce在Oracle中有什么不明显的区别吗？
显而易见的区别在于，coalesce将返回其参数列表中第一个非null项，而nvl只取两个参数，如果第一个不为null，则返回第一个，否则返回第二个。
似乎NVL只是coalesce的“基本情况”版本。
我有所遗漏吗？
8个回答
350
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
)
由于生成 SYS_GUID()，即使 1 不是 NULL，此代码也要运行近 0.5 秒。
SELECT SUM(val)
FROM (
SELECT COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
FROM dual
CONNECT BY
level <= 10000
)
这意味着1不是NULL，因此不会评估第二个参数。
SYS_GUID不需要生成，查询是即时的。
- Quassnoi
2
17它们并不完全是同义词...... 至少你可以发现一个区别，即如果给定的值类型不同，NVL会进行隐式数据类型转换。因此，例如，我在使用COALESCE时遇到错误，将其传递两个NULL值（一个明确设置的和另一个从数据库中的NUMBER类型列中获取的），只需将函数更改为NVL就可以使错误消失。 - DanielM
如果你希望你的SQL更容易迁移到另一个关系数据库管理系统品牌或SQL方言，那么COALESCE通常是最好的选择。但是可以肯定的是，如果你经常需要这样的功能，那么NVL更简洁，我认为在阅读SQL时更加流畅。 - FloverOwe
188
NVL会对第一个参数进行隐式转换，所以下面的代码不会出错
select nvl('a',sysdate) from dual;
COALESCE期望一致的数据类型。
select coalesce('a',sysdate) from dual;
会抛出“数据类型不一致错误”
- Gary Myers
37
NVL和COALESCE用于在列返回NULL的情况下提供默认值的相同功能。
它们之间的区别在于：
- NVL只接受2个参数，而COALESCE可以接受多个参数
- NVL会评估两个参数，而COALESCE会在第一个非NULL值出现时停止评估
- NVL根据给定的第一个参数进行隐式数据类型转换。COALESCE要求所有参数具有相同的数据类型
- 在使用UNION子句的查询中，COALESCE会出现问题。以下是示例。
- COALESCE是ANSI标准，而NVL是Oracle特定的。
第三种情况的示例。其他情况很简单。
select nvl('abc',10) from dual;将按预期工作，因为NVL将数字10隐式转换为字符串。
select coalesce('abc',10) from dual;将失败并显示错误消息：“不一致的数据类型：预期CHAR，但得到了NUMBER”。
使用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
) ;
- Brahmareddy K
1
3我认为“union”并没有特别的问题，更多的是Oracle想默认将你子查询中的null强制转换成char类型，然后你就会遇到与第三点相同的问题（混合数据类型）。如果你将其改为TO_DATE(NULL)，你可能就不会出现错误了（我在我使用的Oracle版本上无法重现这个错误）。否则，我同意并感谢你的回答。 :-) - splashout
19
在计划处理方面也存在差异。
当搜索包含对具有索引列的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提供的帮助。
- Vadzim
6
coalesce()函数不会在第一个非空值处停止求值的另一个证明：
SELECT COALESCE(1, my_sequence.nextval) AS answer FROM dual;
运行此代码后，检查
my_sequence.currval;。- Herb Swift
1
是的，它确实可以。我创建了一个名为my_sequence的序列，从1开始递增1，然后运行您的查询，选择my_sequence.currval，它返回1。 - llepec
5
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我们可以实现所有三个规则。
- sandip
2
你对于
NVL(Purchase_Price + (Purchase_Price * 0.10), nvl(Min_Price,50)) 或者 nvl(NVL(Purchase_Price + (Purchase_Price * 0.10), Min_Price) ,50) 有什么看法？ :) - Florin Ghita哪个更快，从性能角度考虑应该使用什么？考虑到要加载成千上万条记录？ - rickyProgrammer
3
实际上，我不能完全同意每个说法。
"COALESCE期望所有参数都是相同的数据类型。"
这是错误的，请参见下面的说明。参数可以是不同的数据类型，这也在文档中有记录：如果所有的expr出现的次数都是数字数据类型或任何可以隐式转换为数字数据类型的非数字数据类型，则Oracle Database确定具有最高数字优先级的参数，隐式地将其余参数转换为该数据类型，并返回该数据类型。实际上，这甚至与常用表达式“COALESCE在第一个非Null值出现时停止”相矛盾，否则测试用例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!
- Wernfried Domscheit
1
4回复：“Test 4 contradicts "COALESCE stops evaluation at the first non-null value". ”我不同意。测试4表明编译器与COALESCE一起检查数据类型的一致性。停在第一个非空值上是运行时问题，而不是编译时问题。在编译时，编译器不知道第三个值（例如）将是非空的；它坚持要求第四个参数也是正确的数据类型，即使该第四个值实际上永远不会被评估。 - user5683823
3
虽然这个问题很明显，甚至是由提出这个问题的Tom以某种方式提到的。但我们再次提出。
NVL只能有2个参数。而Coalesce可以有多于两个参数。
select nvl('','',1) from dual; //结果：ORA-00909：无效的参数数量
select coalesce('','','1') from dual; //输出：返回1
- Neel
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接