NVL 和 Coalesce 之间的 Oracle 差异

Oracle Differences between NVL and Coalesce(NVL 和 Coalesce 之间的 Oracle 差异)

问题描述

在 Oracle 中 NVL 和 Coalesce 之间是否有不明显的区别?

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

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

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.

似乎 NVL 可能只是合并的基本案例"版本.

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

我错过了什么吗?

推荐答案

COALESCE 是更现代的函数，是 ANSI-92 标准的一部分.

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

NVL 是 Oracle 特有的，它在 80's 在没有任何标准之前被引入.

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

如果有两个值，它们是同义词.

In case of two values, they are synonyms.

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

However, they are implemented differently.

NVL 总是对两个参数求值，而 COALESCE 通常在找到第一个非 NULL 时停止求值(有一些例外，例如作为序列 NEXTVAL):

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

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
        )