使用ISNULL vs使用COALESCE检查特定条件?

I know that multiple parameters can be passed to COALESCE, but when you want to to check just one expression to see if it doesn't exist, do you use a default or is it a better practice to use ISNULL instead?

我知道可以将多个参数传递给COALESCE，但如果您只想检查一个表达式，看看它是否不存在，您是使用默认值呢，还是使用ISNULL代替比较好?

Is there any performance gain between the two?

这两者之间有什么性能提升吗?

9 个解决方案

#1

This problem reported on Microsoft Connect reveals some differences between COALESCE and ISNULL:

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

an early part of our processing rewrites COALESCE( expression1, expression2 ) as CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END. In [this example]:

我们处理的一个早期部分重写合并(expression1, expression2)，当expression1不是NULL就结束了。(这个例子):

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

we generate:

我们生成:

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

Later stages of query processing don't understand that the two subqueries were originally the same expression, so they execute the subquery twice...

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

One workaround, though I hate to suggest it, is to change COALESCE to ISNULL, since the latter doesn't duplicate the subquery.

虽然我不愿意建议，但是有一个解决方案是将合并改为ISNULL，因为后者不重复子查询。

#2

I think not, but COALESCE is in the SQL '92 standard and supported by more different databases. If you go for portability, don't use ISNULL.

我认为不是，但是合并是在SQL '92标准中，并由更多不同的数据库支持。如果你追求可移植性，不要使用ISNULL。

#3

In COALESCE you can have multiple expressions, where as in ISNULL you can check only one expression

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

COALESCE ( expression [ ,...n ] ) 

ISNULL ( check_expression , replacement_value )

#4

Worth mentioning is that the type handling between the two can also make a difference (see this related answer item (2)).

值得一提的是，两者之间的类型处理也可以产生差异(请参阅相关的回答项目(2))。

Say a query tries to use a shortcut for writing null comparison:

假设查询试图使用快捷方式写入空比较:

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

which is different than

这是不同的

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

Because in the first case, the IsNull() forces the type to be a bit (so -1 is converted to true) whereas the second case will promote both to an int.

因为在第一种情况下，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;

There is a similar comment/link (@Martin Smith) on the question itself.

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

#5

One major thing that I don't see explicitly indicated is that ISNULL's output type is similar to the first expression but with COALESCE it returns the datatype of value of highest precedence.

我没有看到显式显示的一个主要内容是，ISNULL的输出类型与第一个表达式类似，但是通过合并，它返回最高优先级值的数据类型。

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'

#6

This explanation gives clear about coalesce vs isnull

这种解释清楚地说明了合并与isnull的区别

The COALESCE function in SQL returns the first non-NULL expression among its arguments. The syntax for COALESCE is as follows:

SQL中的COALESCE函数返回其参数中的第一个非空表达式。合并的语法如下:

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

It is the same as the following CASE statement:

与下列情况相同:

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

In SQL Server, the ISNULL( ) function is used to replace NULL value with another value.

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

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

Coalesce return first non null expression where as isnull() is used to replace null value with our desired value.

联合返回第一个非空表达式，其中isnull()用于用我们希望的值替换空值。

COALESCE is a part of ANSI standards and are available in almost all databases.

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

when deciding between ISNULL v COALESCE there parameters has to be taken care off:

当在ISNULL v合并时，必须考虑参数:

1. 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.
2. 联合决定基于数据类型优先级的输出类型，其中与ISNULL一样，数据类型不受数据类型优先级的影响。

3. Consider following sql statements

   考虑以下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

This happens because ISNULL takes the data type of the first argument, while COALESCE inspects all of the elements and chooses the best fit (in this case, VARCHAR(11))

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

For more detailed explanation on deciding between COALESCE vs ISNULL check this: https://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

要了解关于在COALESCE和ISNULL之间进行决策的更详细的解释，请查看以下内容:https://www.mssqltips.com/sqlservertip/2689/ deciesce -and- ISNULL -in-sql-server/

#7

Where there is only one null condition, ISNULL will have less overhead. The difference is probably negligible, though.

如果只有一个空条件，ISNULL就会减少开销。不过，这种差异可能可以忽略不计。

#8

The NULL and COALESCE are not always interchangeable. It deserves to know their differences in order to know when its better to use the one over the other:

空和合并并不总是可以互换的。它应该知道它们的不同之处，以便知道什么时候最好使用其中一个而不是另一个:

The table above is comparison between ISNULL and COALESCE from Exam Ref 70-761 Querying Data with Transact-SQL book written by Itzik Ben-Gan.

上面的表格是ISNULL和与由Itzik Ben-Gan编写的Transact-SQL book查询数据的testref 70-761查询数据的比较。

---

1. Number of supported parameters - 2 for ISNULL vs >2 when using COALESCE
2. 使用合并时，ISNULL和>2支持的参数的数量- 2
3. ISNULL is proprietary T-SQL feature and COALESCE is ISO/ANSI SQL standard
4. ISNULL是专有的T-SQL特性，联合是ISO/ANSI SQL标准

5. 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.

   ISNULL获取第一个参数的数据类型，因为它是非空文本。它是VARCHAR(3)，是一个结果，第二个参数数据被切割以匹配它。如果使用了最高优先级，将合并数据类型。

   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.

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

6. 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.

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

7. According to the SQL standard, the COALESCE expression is translated to:

   根据SQL标准，将COALESCE表达式翻译为:

   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.

   如果执行WHEN子查询的结果不是NULL，那么SQL Server将在THEN子句中第二次执行它。换句话说，在这种情况下，它执行两次。只有当WHEN子句中的执行结果为空时，SQL Server才不会再次执行子查询，而是返回ELSE表达式。因此，当使用子查询时，ISNULL函数具有性能优势。

#9

In COALESCE one can use multiple expressions, It will return value which is not a null and occurs first... for example

在合并中，可以使用多个表达式，它返回的值不是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)

And in ISNULL if expression null it will return second parameter provided, and of course you can check only for one expression...

在ISNULL if表达式null中，它将返回第二个参数，当然，您只能检查一个表达式……

So if want check multiple expression and select first not null among them, then use coalesce otherwise go for ISNULL

因此，如果要检查多个表达式，并首先在它们之间选择not null，那么使用coalesce，否则就选择ISNULL