使用ISNULL还是使用COALESCE来检查特定条件？

Question

使用ISNULL还是使用COALESCE来检查特定条件？

sqlt-sqlcoalesceisnull

87

我知道COALESCE可以传递多个参数，但是当你只想检查一个表达式是否存在时，是使用默认值还是最好使用ISNULL呢？

这两者之间是否有性能上的差异?

- JBone

5

COALESCE文档中有这样一条注释：ISNULL和COALESCE虽然等价，但可能会有不同的行为。涉及非空参数的ISNULL表达式被认为是非空的，而涉及非空参数的COALESCE表达式被认为是空的... - user166390

3

ISNULL函数将结果强制转换为第一个表达式的数据类型，具体演示见这里。 - Martin Smith

4

这篇文章很好地阐述了Coalesce和IsNull之间的区别...http://sqlmag.com/t-sql/coalesce-vs-isnull - Data Masseur

这也是一篇不错的文章...http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/ - goodeye

9个回答

28

我认为不行，但COALESCE是SQL '92标准中支持的，并且被更多不同的数据库所支持。如果你想要可移植性，不要使用ISNULL。

- GolezTrol

@AaronAnodide MySQL 使用 ifnull，SQL Server 使用 isnull。 - nawfal

Oracle的COALESCE替代方案是NVL。因此，即使在数据库中实现细节不同，COALESCE作为标准的观点仍然是有效的。 - Suncat2000

13

COALESCE允许包含多个表达式，而ISNULL只能检查一个表达式。

COALESCE ( expression [ ,...n ] ) 

ISNULL ( check_expression , replacement_value )

- otti

10

我看不到明确表述的一件重要事情是，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'

- BICube

4

不是第一表达式与第二/N个表达式的问题。参见这里：

ISNULL使用第一个参数的数据类型，COALESCE遵循CASE表达式规则并返回具有最高优先级的值的数据类型。

- underscore_d

10

值得一提的是，两者之间的类型处理也可能会有所不同（请参见此相关答案项（2））。假设查询尝试使用简写来进行空值比较：

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

这与之不同

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

因为在第一种情况下，IsNull() 函数会强制类型为 bit（所以 -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）。

- crokusek

9

NULL和COALESCE并不总是可以互换的。了解它们的区别很重要，以便在何时更好地使用其中之一：

上面这张表格是来自Itzik Ben-Gan的书《Exam Ref 70-761 Querying Data with Transact-SQL》中的ISNULL和COALESCE的比较。

Number of supported parameters - 2 for ISNULL vs >2 when using COALESCE
ISNULL is proprietary T-SQL feature and COALESCE is ISO/ANSI SQL standard
The data type of the result is important. After reading notes in the table above, check the following cases:
```
DECLARE @x VARCHAR(3)  = NULL
       ,@y VARCHAR(10) = '1234567890';

SELECT ISNULL(@x, @y) AS [ISNULL], COALESCE(@x, @y) AS [COALESCE];
```
The ISNULL is getting the data type of the first argument as it is the not NULL literal. It is VARCHAR(3) and is a result, the second argument data is cut to match it. With COALESCE the data type if highest precedence is used.
```
DECLARE @x VARCHAR(8)  = '123x5'
       ,@y INT = 123;

SELECT ISNULL(@x, @y) AS [ISNULL];
SELECT COALESCE(@x, @y) AS [COALESCE];
```
The ISNULL is returning the data type of first argument, while in COALESCE we are getting error, as the INT has highest precedence and the conversion of the first argument value to INT fails.

The nullability of the result can be important, too. For, example:

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];

Let's check the Nullable property of each column:

Using COALESCE we have a NOT NULL property of column set to Yes, only when all of the inputs are non null-able.

According to the SQL standard, the COALESCE expression is translated to:
```
CASE WHEN (<subquery>) IS NOT NULL THEN (<subquery>) ELSE 0 END
```
If the result of the execution of the subquery in the WHEN clause isn’t NULL, SQL Server executes it a second time in the THEN clause. In other words, in such a case it executes it twice. Only if the result of the execution in the WHEN clause is NULL, SQL Server doesn’t execute the subquery again, rather returns the ELSE expression. So when using subqueries, the ISNULL function has a performance advantage.

- gotqn

3

这段说明清楚了coalesce和isnull之间的区别。

在SQL中，COALESCE函数返回参数中第一个非空表达式。COALESCE的语法如下：

 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";

在SQL Server中，ISNULL()函数用于将NULL值替换为另一个值。

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

COALESCE函数返回第一个非空表达式，而isnull()函数用于将null值替换为我们期望的值。

COALESCE是ANSI标准的一部分，几乎所有数据库都支持它。

在选择ISNULL和COALESCE之间时，必须注意以下参数：

COALESCE determines the type of the output based on data type precedence where as With ISNULL, the data type is not influenced by data type precedence.

Consider following sql statements

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

结果：

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/

- Nayas Subramanian

2

如果只有一个空值条件，ISNULL 的开销会更小。尽管差别可能微不足道。

- James Johnson

1

你有任何支持“ISNULL”具有更少开销的声明吗？ - Joshua Drake

1

@JoshuaDrake：使用“COALESCE”替换“IIF”的区域有两个，其中“ISNULL”处理固定数量的输入，“COALESCE”被指定为使用任意数量的输入。其次，“COALESCE”被配置为返回具有最高数据类型优先级的表达式的数据类型，而“ISNULL”返回与“check_expression”相同的类型。如上所述，在SQL Server的后续版本中，差异可能可以忽略不计，但严格来说仍存在开销。 - James Johnson

-2

在 COALESCE 中，可以使用多个表达式，它将返回第一个不为 null 的值... 例如。

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，则返回提供的第二个参数，当然你只能检查一个表达式...

因此，如果想要检查多个表达式并选择其中第一个非空值，则使用 COALESCE，否则使用 ISNULL。

- Ranadeera Kantirava

2

OP 表示他们知道 COALESCE 能够处理多个参数，问题是关于只有两个参数的特定情况。 - Joshua Drake

@JoshuaDrake 请完整阅读我的回答...我阅读了问题，请求您完整阅读我的回答...很容易忽略一些要点并将其降低评分。 - Ranadeera Kantirava

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- onedaywhen · Accepted Answer

这个在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，因为后者不会重复执行子查询。