使用 ISNULL 与使用 COALESCE 检查特定条件？

我知道可以将多个参数传递给 COALESCE，但是如果需要的话 只检查一个表达式，看看它是否不存在，是使用默认表达式，还是使用 ISNULL更好？

这两者之间是否存在性能增益？

54730 次浏览
小开

我不这么认为，但 COALESCE 采用了 SQL’92标准，并得到了更多不同数据库的支持。如果您追求可移植性，就不要使用 ISNULL。

小开

在只有一个空条件的情况下，ISNULL的开销会更小。

小开
最佳答案

Microsoft Connect 上报道的这个问题揭示了 COALESCE和 ISNULL之间的一些差异:

我们处理的早期部分将 COALESCE( expression1, expression2 )重写为 CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END:

COALESCE ( ( SELECT Nullable
FROM Demo
WHERE SomeCol = 1 ), 1 )

我们产生:

SELECT CASE
WHEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) IS NOT NULL
THEN (SELECT Nullable FROM Demo WHERE SomeCol = 1)
ELSE 1
END

后面的查询处理阶段不理解这两个子查询最初是相同的表达式，因此它们执行两次子查询..。

一个变通方法是将 COALESCE更改为 ISNULL，因为后者不会重复子查询，尽管我不想这样建议。

小开

在 COALESCE中可以有多个表达式，而在 ISNULL中只能检查一个表达式

COALESCE ( expression [ ,...n ] )


ISNULL ( check_expression , replacement_value )
小开

在 COALESCE 中，可以使用多个表达式，它将返回不为空的值，并且首先出现... ... 例如

DECLARE @Value1 INT, @Value2 INT, @Value3 INT, @Value4 INT
SELECT @Value2 = 2, @Value4 = 4
SELECT COALESCE(@Value1, @Value2, @Value3, @Value4)
SELECT COALESCE(@Value1, @Value4, @Value3, @Value2)

在 ISNULL 中，如果表达式为 null，它将返回提供的第二个参数，当然，您只能检查一个表达式..。

因此，如果要检查多个表达式，并选择其中首先不为空，然后使用合并否则去为 ISNULL

小开

值得一提的是，两者之间的类型处理也会产生差异(参见 这个相关的答案项目(2))。

假设一个查询尝试使用快捷方式写空比较:

select * from SomeTable
where IsNull(SomeNullableBitField, -1) != IsNull(SomeOtherNullableBitField, -1);

这不同于

select * from SomeTable
where coalesce(SomeNullableBitField, -1) != coalesce(SomeOtherNullableBitField, -1);

因为在第一种情况下，IsNull ()强制类型为位(因此 -1被转换为 true) ，而在第二种情况下，两者都将升级为 int。

with input as
(
select convert(bit, 1) as BitOn,
convert(bit, 0) as BitOff,
convert(bit, null) as BitNull
)
select BitOn,
BitOff,
BitNull,
IsNull(BitOn, -1) IsNullBitOn,         -- true
IsNull(BitOff, -1) IsNullBitOff,       -- false
IsNull(BitNull, -1) IsNullBitNull,     -- true, converts the -1 to bit
coalesce(BitOn, -1) CoalesceBitOn,     -- 1
coalesce(BitOff, -1) CoalesceBitOff,   -- 0
coalesce(BitNull, -1) CoalesceBitNull  -- -1
from input;

这个问题本身也有一个类似的评论/链接(@Martin Smith)。

小开

我没有看到明确指出的一个主要问题是，ISNULL的输出类型类似于第一个表达式，但是对于 COALESCE，它返回优先级最高的值的数据类型。

DECLARE @X VARCHAR(3) = NULL
DECLARE @Y VARCHAR(10) = '123456789'
/* The datatype returned is similar to X, or the first expression*/
SELECT ISNULL(@X, @Y) ---> Output is '123'
/* The datatype returned is similar to Y, or to the value of highest precedence*/
SELECT COALESCE(@X, @Y) ---> Output is '123456789'
小开

这个解释清楚地说明了合并与 isnull 之间的关系

SQL 中的 COALESCE 函数返回其参数中的第一个非 NULL 表达式:

 COALESCE ("expression 1", "expressions 2", ...)

它与以下 CASE 陈述相同:

SELECT CASE ("column_name")
WHEN "expression 1 is not NULL" THEN "expression 1"
WHEN "expression 2 is not NULL" THEN "expression 2"
...
[ELSE "NULL"]
END
FROM "table_name";

在 SQLServer 中，ISNULL ()函数用另一个值替换 NULL 值。

select CountryName = ISNULL("columnname", 'INDIA') from Countries

Coalesce 返回第一个非 null 表达式，其中 isnull ()用我们所需的值替换 null 值。

COALESCE 是 ANSI 标准的一部分，可在几乎所有数据库中使用。

在决定 ISNULL 和 COALESCE 之间时，必须考虑以下参数:

  1. COALESCE 根据数据类型优先级确定输出的类型，其中与 ISNULL 一样，数据类型不受数据类型优先级的影响。

  2. 请考虑以下 sql 语句

    DECLARE @c5 VARCHAR(5);
SELECT 'COALESCE', COALESCE(@c5, 'longer name')
UNION ALL
SELECT 'ISNULL',   ISNULL(@c5,   'longer name');

Results:

COALESCE longer name
ISNULL   longe

这是因为 ISNULL 采用第一个参数的数据类型，而 COALESCE 检查所有元素并选择最佳匹配(在本例中为 VARCHAR (11))

有关在 COALESCE 和 ISNULL 之间做出决定的更详细解释，请查看以下内容: Https://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

小开

NULL和 COALESCE并不总是可以互换的。它应该知道他们的不同，以便知道什么时候用一个比用另一个更好:

enter image description here

上面的表格是比较 ISNULL和 COALESCE从 Exam Ref 70-761 Querying Data with Transact-SQL书由 Itzik Ben-Gan 写的。

  1. 当使用 COALESCE时，支持的参数数目 -ISNULL与 >2的 2
  2. ISNULL是专有的 T-SQL 特性，COALESCE是 ISO/ANSI SQL 标准

  3. 结果的数据类型很重要。阅读上表的注释后，检查以下情况:

    DECLARE @x VARCHAR(3)  = NULL
,@y VARCHAR(10) = '1234567890';


SELECT ISNULL(@x, @y) AS [ISNULL], COALESCE(@x, @y) AS [COALESCE];

    enter image description here

    ISNULL获取第一个参数的数据类型，因为它是 not 它是 VARCHAR(3)，是结果，第二个参数数据 对于 COALESCE，如果最高优先级为 用过。

    DECLARE @x VARCHAR(8)  = '123x5'
,@y INT = 123;


SELECT ISNULL(@x, @y) AS [ISNULL];
SELECT COALESCE(@x, @y) AS [COALESCE];

    enter image description here

    enter image description here

    ISNULL返回第一个参数的数据类型，而 我们得到错误，因为 INT具有最高的优先级和 将第一个参数值转换为 INT失败。

  4. 结果的可零性也很重要，例如:

    DECLARE @x VARCHAR(3) = NULL
,@y VARCHAR(3) = NULL;


DROP TABLE IF EXISTS [dbo].[DataSource01];


SELECT ISNULL(10, 20) AS [C1]
,ISNULL(@x, 'text') AS [C2]
,ISNULL(@x, @y) AS [C3]
INTO [dbo].[DataSource01];


DROP TABLE IF EXISTS [dbo].[DataSource02];


SELECT COALESCE(10, 20) AS [C1]
,COALESCE(@x, 'text') AS [C2]
,COALESCE(@x, @y) AS [C3]
INTO [dbo].[DataSource02];

    让我们检查每一列的 Nullable属性:

    enter image description here

    enter image description here

    使用 COALESCE，我们只能将列的 NOT NULL属性设置为 Yes 当所有的输入都是非空的时候。

  5. 根据 SQL 标准，COALESCE表达式被翻译为:

    CASE WHEN (<subquery>) IS NOT NULL THEN (<subquery>) ELSE 0 END

    如果 WHERE 子句中子查询的执行结果不是 SQLServer 在 THEN 子句中第二次执行它。 换句话说，在这种情况下，它会执行两次 执行的结果是 NULL，而 SQLServer 不是 再次执行子查询，而是返回 ELSE 表达式 在使用子查询时，ISNULL 函数具有性能 优势。


提交答案