ISNULL() VS COALESCE()

在处理Sql Server中的一些值或表的列时碰到NULL值时，开发者们必须进行判断并返回另一个值，这时得用到两个函数：IsNull() 和 Coalesce()

 

1、  ISNULL()

IsNull 是SQL SERVER内置的TSQL函数，但不是ANSI-92定义的，是MS扩展ANSI-92自行定义的一个新方法。

IsNull()有两个参数，第一个是将被检查是否为 NULL 的表达式，如果这个表达式的值是NULL那么第二个表达式(不管是不是NULL)就会返回。请看如下示例：

 

SELECT ISNULL(NULL, 1)

--Returns 1

SELECT ISNULL(NULL, NULL)

--Returns NULL

 

2、COALESCE()

COALESCE() 像ISNULL一样是SQLSERVER内置的一个函数，不像ISNULL的是，Coalesce是ANSI-92标准的一部分，这个函数返回表达式列表的第一个非NULL的表达式，表达式列表可是多两个或更多，并且表达式的值可以是不同类型的。请看如下示例：

 

SELECT COALESCE(NULL, 1)

--Returns 1

SELECT COALESCE(NULL, 3, NULL, 1)

--Returns 3

 

3、ISNULL vs. COALESCE

当有一个问题有多种方法解决时必然存在一个选择的问题，不可回避的就是：哪个更优？比较这两个函数的差异会发现Coalesce通常优于IsNULL:
-COALESCE 是ANSI-92标准定义. 当把你的应用移植于其它RDBMS时COALESCE不需要修改。
-COALESCE 能接受多于2个表达式，ISNULL却只能接受两个，为了能比较多于两个表达式ISNULL必须采用如下形式：

 SELECT ISNULL(ISNULL(Col1, Col2), Col3) 

 

-ISNULL 强制结果的数据类型为第一个参数的类型。看如下例子当使用ISNULL时会得到一个我们意外的结果，但是用Coalesce却可得到期望的结果。

 

DECLARE @Field1 char(3), @Field2 char(50)

SET @Field2 = 'Some Long String'

 

SELECT ISNULL(@Field1, @Field2)

--Returns 'Som'

SELECT COALESCE(@Field1, @Field2)

--Returns 'Some Long String'

 

注：在一些情况下，COALESCE将产生一些意外结果。

会把所有表达式向一个公共的能包容所类型的数据类型转换（因为是隐式的转换情况，有些类型是不兼容，当然会抛出一个错误）。

当COALESCE的参数是一个整数和一个日期时间时，只能COALESCE隐式的把整数转换为一个日期时间。

例如：

SELECT COALESCE( SELECT COALESCE(5, GETDATE()) Se（（(5, GETDATE())  

不会返回5 ，它返回1900-1-6 00:00:00.000 （ 5作为一个日期时间） 。

 

4、性能

多数情况下，这两个函数的执行是差不多的，一般情况下多数的人认为ISNULL是稍优于Coalesce的，虽然我们没有一个充分的理由来证明这些，但是当性能成为一个不能不考虑的问题时，我们可以用测试数据进行测试这两种办法的性能。 

 

疑点：

事实上我做过74W行的数据测试，性能上基本没有什么差别，并且对两个表达式的判断ISNULL略显优势.

其实
COALESCE(EXPRESSION1，…N) 等价于：

CASE

WHEN (EXPRESSION1 IS NOT NULL) THEN EXPRESSION1

……

ELSE Null

END

但是ISNULL的执行过程是如何工作的呢，难道是IF … else …  ??

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10527166/viewspace-706910/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10527166/viewspace-706910/