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

Question

提问by JBone

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 ISNULLinstead?

我知道可以将多个参数传递给COALESCE，但是当您只想检查一个表达式以查看它是否不存在时，您是使用默认值还是ISNULL改用更好的做法？

Is there any performance gain between the two?

两者之间是否有任何性能提升？

Answer 1

采纳答案by onedaywhen

This problem reported on Microsoft Connectreveals some differences between COALESCEand 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]:
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 COALESCEto ISNULL, since the latter doesn't duplicate the subquery.

我们处理的早期部分重写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，因为后者不复制子查询。

Answer 2

回答by GolezTrol

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.

我认为不是，但是 COALESCE 符合 SQL '92 标准，并且受到更多不同数据库的支持。如果您追求可移植性，请不要使用 ISNULL。

Answer 3

回答by otti

In COALESCEyou can have multiple expressions, where as in ISNULLyou can check only one expression

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

COALESCE ( expression [ ,...n ] ) 

ISNULL ( check_expression , replacement_value )

Answer 4

回答by crokusek

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() 强制类型为一点（因此 -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）。

Answer 5

回答by BICube

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

我没有看到明确指出的一个主要事情是 thatISNULL的输出类型类似于第一个表达式，但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'

Answer 6

回答by gotqn

The NULLand COALESCEare not always interchangeable. It deserves to know their differences in order to know when its better to use the one over the other:

该NULL和COALESCE并不总是可互换。应该了解它们的差异，以便知道何时使用一个比另一个更好：

The table above is comparison between ISNULLand COALESCEfrom Exam Ref 70-761 Querying Data with Transact-SQLbook written by Itzik Ben-Gan.

上表为之间的比较ISNULL，并COALESCE从Exam Ref 70-761 Querying Data with Transact-SQL由伊茨克奔甘写的书。

Number of supported parameters - 2for ISNULLvs >2when using COALESCE
ISNULLis proprietary T-SQL feature and COALESCEis 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 ISNULLis getting the data type of the first argument as it is the not NULLliteral. It is VARCHAR(3)and is a result, the second argument data is cut to match it. With COALESCEthe 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 ISNULLis returning the data type of first argument, while in COALESCEwe are getting error, as the INThas highest precedence and the conversion of the first argument value to INTfails.

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 Nullableproperty of each column:

Using COALESCEwe have a NOT NULLproperty of column set to Yes, only when all of the inputs are non null-able.

According to the SQL standard, the COALESCEexpression 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.

支持的参数数量 - 2for ISNULLvs>2使用时COALESCE
ISNULL是专有的 T-SQL 功能，COALESCE是 ISO/ANSI SQL 标准
结果的数据类型很重要。阅读上表中的注释后，请检查以下情况：
```
DECLARE @x VARCHAR(3)  = NULL
       ,@y VARCHAR(10) = '1234567890';

SELECT ISNULL(@x, @y) AS [ISNULL], COALESCE(@x, @y) AS [COALESCE];
```
在ISNULL越来越的第一个参数的数据类型，因为它是不 NULL字面。它是VARCHAR(3)和是结果，第二个参数数据被剪切以匹配它。随着COALESCE数据类型，如果使用最高优先级。
```
DECLARE @x VARCHAR(8)  = '123x5'
       ,@y INT = 123;

SELECT ISNULL(@x, @y) AS [ISNULL];
SELECT COALESCE(@x, @y) AS [COALESCE];
```
在ISNULL将返回第一个参数的数据类型，而 COALESCE我们得到的错误，因为INT具有最高的优先级，并第一个参数值的转换INT失败。

结果的可空性也很重要。例如：

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每一列的属性：

使用COALESCE我们将NOT NULLcolumn的属性设置为Yes，仅当所有输入都不能为空时。

根据 SQL 标准，COALESCE表达式被翻译为：
```
CASE WHEN (<subquery>) IS NOT NULL THEN (<subquery>) ELSE 0 END
```
如果 WHEN 子句中子查询的执行结果不为 NULL，SQL Server 将在 THEN 子句中再次执行它。换句话说，在这种情况下它会执行两次。只有当 WHEN 子句中的执行结果为 NULL 时，SQL Server 才不会再次执行子查询，而是返回 ELSE 表达式。所以在使用子查询时，ISNULL 函数具有性能优势。

Answer 7

回答by Nayas Subramanian

This explanation gives clear about coalesce vs isnull

这个解释清楚地说明了coalesce vs isnull

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

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

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

It is the same as the following CASE statement:

它与以下 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";

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

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

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.

Coalesce 返回第一个非空表达式，其中 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 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 根据数据类型优先级确定输出的类型，而对于 ISNULL，数据类型不受数据类型优先级的影响。

考虑以下 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 采用第一个参数的数据类型，而 COALESCE 检查所有元素并选择最合适的（在本例中为 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/deciding-between-coalesce-and-isnull-in-sql-server/

Answer 8

回答by James Johnson

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

只有一个空条件时，ISNULL开销会更少。不过，差异可能可以忽略不计。

Answer 9

回答by Ranadeera Kantirava

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

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

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

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

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

因此，如果要检查多个表达式并首先选择其中的非空，则使用合并，否则选择 ISNULL

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

提问by JBone

采纳答案by onedaywhen

回答by GolezTrol

回答by otti

回答by crokusek

回答by BICube

回答by gotqn

回答by Nayas Subramanian

回答by James Johnson

回答by Ranadeera Kantirava

相关推荐

最近更新

标签

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

提问by JBone

采纳答案by onedaywhen

回答by GolezTrol

回答by otti

回答by crokusek

回答by BICube

回答by gotqn

回答by Nayas Subramanian

回答by James Johnson

回答by Ranadeera Kantirava

相关推荐

SQL PostgreSQL MAX 和 GROUP BY

SQL 查找插入特定表的存储过程

Oracle：SQL 选择带时间戳的日期

SQL 如何在左联接中选择单个记录

相关推荐

最近更新

标签