SQL - COALESCE和ISNULL之间的区别？ [重复]

This question already has an answer here:

这个问题在这里已有答案：

Using ISNULL vs using COALESCE for checking a specific condition? 9 answers
使用ISNULL vs使用COALESCE检查特定条件？ 9个答案

What are the practical differences between COALESCE() and ISNULL(,'')?

COALESCE（）和ISNULL（，''）之间有什么实际区别？

When avoiding NULL values in SQL concatenations, which one is the best to be used?

在SQL连接中避免使用NULL值时，哪一个最适合使用？

Thanks!

谢谢！

4 个解决方案

#1

Comparing COALESCE and ISNULL

比较COALESCE和ISNULL

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

ISNULL函数和COALESCE表达式具有相似的目的，但行为可能不同。

Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.

因为ISNULL是一个函数，所以它只被评估一次。如上所述，可以多次评估COALESCE表达式的输入值。

Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

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

The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different nullability values. This makes a difference if you are using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example.

对于ISNULL和COALESCE，结果表达式的可空性是不同的。 ISNULL返回值始终被视为NOT NULLable（假设返回值是非可空的），而具有非null参数的COALESCE被视为NULL。因此，表达式ISNULL（NULL，1）和COALESCE（NULL，1）虽然等效，但具有不同的可为空性值。如果您在计算列中使用这些表达式，创建键约束或使标量UDF的返回值具有确定性，以便可以对其进行索引（如以下示例所示），则会产生差异。

> USE tempdb; 
> GO

> -- This statement fails because the PRIMARY KEY cannot accept NULL values
> -- and the nullability of the COALESCE expression for col2 
> -- evaluates to NULL. 

> CREATE TABLE #Demo  (  col1 integer NULL,  col2 AS COALESCE(col1, 0) PRIMARY KEY,  col3 AS ISNULL(col1, 0)  ); 
> 
> -- This statement succeeds because the nullability of the 
> -- ISNULL function evaluates AS NOT NULL.
> 
> CREATE TABLE #Demo  (  col1 integer NULL,  col2 AS COALESCE(col1, 0), 
> col3 AS ISNULL(col1, 0) PRIMARY KEY  );

Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int whereas for COALESCE, you must provide a data type. ISNULL takes only 2 parameters whereas COALESCE takes a variable number of parameters.

ISNULL和COALESCE的验证也不同。例如，ISNULL的NULL值转换为int，而对于COALESCE，您必须提供数据类型。 ISNULL只接受2个参数，而COALESCE接受可变数量的参数。

Source: BOL

资料来源：BOL

#2

The main difference is, that COALESCE is ANSI-Standard, so you will also find it in other RDBMSs, the other difference is you can give a whole list of values to be checked to COALESCE whereas to ISNULL you can only pass one.

主要区别在于，COALESCE是ANSI-Standard，因此您也可以在其他RDBMS中找到它，另一个区别是您可以将一整个值列表检查到COALESCE，而对于ISNULL，您只能传递一个。

#3

Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times. COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine.

因为ISNULL是一个函数，所以它只被评估一次。如上所述，可以多次评估COALESCE表达式的输入值。 COALESCE基本上转换为CASE表达式，ISNULL是在数据库引擎中实现的内置。

ISNULL is faster than COALESCE.

ISNULL比COALESCE更快。

MSDN

#4

COALESCE() can have multiple inputs and it will evaluate in order until one of them is not null such as COALESCE(Col1, Col2, Col3, 'N/A'). It's recommended to use this by MS instead of ISNULL()

COALESCE（）可以有多个输入，它将按顺序进行评估，直到其中一个不为空，例如COALESCE（Col1，Col2，Col3，'N / A'）。建议用MS代替ISNULL（）

ISNULL() can only have one input, however it's been shown to be slightly faster than COALESCE.

ISNULL（）只能有一个输入，但是它显示出比COALESCE略快。

#1

Comparing COALESCE and ISNULL

比较COALESCE和ISNULL

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

ISNULL函数和COALESCE表达式具有相似的目的，但行为可能不同。

Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.

因为ISNULL是一个函数，所以它只被评估一次。如上所述，可以多次评估COALESCE表达式的输入值。

Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

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

The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different nullability values. This makes a difference if you are using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example.

对于ISNULL和COALESCE，结果表达式的可空性是不同的。 ISNULL返回值始终被视为NOT NULLable（假设返回值是非可空的），而具有非null参数的COALESCE被视为NULL。因此，表达式ISNULL（NULL，1）和COALESCE（NULL，1）虽然等效，但具有不同的可为空性值。如果您在计算列中使用这些表达式，创建键约束或使标量UDF的返回值具有确定性，以便可以对其进行索引（如以下示例所示），则会产生差异。

> USE tempdb; 
> GO

> -- This statement fails because the PRIMARY KEY cannot accept NULL values
> -- and the nullability of the COALESCE expression for col2 
> -- evaluates to NULL. 

> CREATE TABLE #Demo  (  col1 integer NULL,  col2 AS COALESCE(col1, 0) PRIMARY KEY,  col3 AS ISNULL(col1, 0)  ); 
> 
> -- This statement succeeds because the nullability of the 
> -- ISNULL function evaluates AS NOT NULL.
> 
> CREATE TABLE #Demo  (  col1 integer NULL,  col2 AS COALESCE(col1, 0), 
> col3 AS ISNULL(col1, 0) PRIMARY KEY  );