SQL - COALESCE 和 ISNULL 之间的区别？

Question

这个问题已经有答案了:

Using ISNULL vs using COALESCE for checking a specific condition? (9 个回答)

已关闭 9 年前。

COALESCE() 和 ISNULL(,'') 之间有什么实际区别？

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

谢谢!

Answer 1

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

2. Data type determination of the resulting expression isdifferent. ISNULL uses the data type of the first parameter, COALESCEfollows the CASE expression rules and returns the data type of valuewith the highest precedence.

3. The NULLability of the result expression is different for ISNULL and COALESCE. TheISNULL return value is always considered NOT NULLable (assuming the return value is anon-nullable one) whereas COALESCE with non-null parameters isconsidered to be NULL. So the expressions ISNULL(NULL, 1) andCOALESCE(NULL, 1) although equivalent have different nullabilityvalues. This makes a difference if you are using these expressions incomputed columns, creating key constraints or making the return valueof a scalar UDF deterministic so that it can be indexed as shown inthe 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 andCOALESCE are also different. For example, a NULL value for ISNULL isconverted to int whereas for COALESCE, you must provide a data type.ISNULL takes only 2 parameters whereas COALESCE takes a variablenumber of parameters.

来源:BOL