SQL - COALESCE 和 ISNULL 之间的区别? [英] SQL - Difference between COALESCE and ISNULL?

问题描述

COALESCE() 和 ISNULL(,'') 之间的实际区别是什么?

在 SQL 连接中避免 NULL 值时，最好使用哪一种?

谢谢！

解决方案

比较 COALESCE 和 ISNULL

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

1. 因为 ISNULL 是一个函数，所以它只被评估一次.如上所述，COALESCE 表达式的输入值可以计算多个次.
2. 结果表达式的数据类型确定为不同的.ISNULL 使用第一个参数 COALESCE 的数据类型遵循CASE表达式规则，返回value的数据类型具有最高优先级.
3. 结果表达式的 NULLability 对于 ISNULL 和 COALESCE 是不同的.这ISNULL 返回值总是被认为是 NOT NULLable(假设返回值是一个不可为空的)，而具有非空参数的 COALESCE 是被认为是 NULL.所以表达式 ISNULL(NULL, 1) 和COALESCE(NULL, 1) 虽然等效具有不同的可空性值.如果您在中使用这些表达式，这会有所不同计算列，创建键约束或制作返回值标量 UDF 确定性的，以便它可以被索引，如下面的例子.

<代码>>使用临时数据库；>走>-- 此语句失败，因为 PRIMARY KEY 不能接受 NULL 值>-- 以及 col2 的 COALESCE 表达式的可空性>-- 评估为 NULL.>CREATE TABLE #Demo ( col1 integer NULL, col2 AS COALESCE(col1, 0) PRIMARY KEY, col3 AS ISNULL(col1, 0) );>>-- 此语句成功，因为>-- ISNULL 函数评估 AS NOT NULL.>>CREATE TABLE #Demo ( col1 integer NULL, col2 AS COALESCE(col1, 0),>col3 AS ISNULL(col1, 0) PRIMARY KEY );

<块引用>

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

来源:BOL

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

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

Thanks!

解决方案

Comparing COALESCE and ISNULL

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

1. 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.
2. 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.
3. 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.

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

Source: BOL

这篇关于SQL - COALESCE 和 ISNULL 之间的区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！