`
javababy1
  • 浏览: 1161836 次
  • 性别: Icon_minigender_1
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

ISNULL() VS COALESCE()

阅读更多

ISNULL() VS COALESCE()

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ISNULL_COALESCE&referringTitle=Home#ISNULL_Definition

在处理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 by nature promotes it's arguments to the highest datatype among compatable arguments (arguments which are not explicitly case, and which aren't compatable, will of course throw an error). COALESCE会把所有表达式向一个公共的能包容所类型的数据类型转换(因为是隐式的转换情况,有些类型是不兼容,当然会抛出一个错误)。 When using COALESCE on an integer and a datetime, in that order, COALESCE will cast the integer as a datetime.COALESCE的参数是一个整数和一个日期时间时,只能COALESCE隐式的把整数转换为一个日期时间。 For example:例如:


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

Will not return 5, it will return 1900-01-06 00:00:00.000 (5 as a datetime).

不会返回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 … ??

For Further Reading

ISNULL (MSDN)
COALESCE (MSDN)

分享到:
评论

相关推荐

    ISNULL()、NVL()、IFNULL() 和 COALESCE() 函数

    ISNULL()、NVL()、IFNULL() 和 COALESCE() 函数的几种用法,

    浅析SQL Server的分页方式 ISNULL与COALESCE性能比较

    本文上述重点讲述了SQL Server的分页方式,COALESCE和ISNULL函数区别之处,简短的内容,深入的理解。有兴趣的朋友可以看下

    mysql中替代null的IFNULL()与COALESCE()函数详解

    在MySQL中isnull()函数不能作为替代null值! 如下: 首先有个名字为business的表: SELECT ISNULL(business_name,'no business_name') AS bus_isnull FROM business WHERE id=2 直接运行就会报错: 错误代码...

    SQL Server COALESCE函数详解及实例

    很多人知道ISNULL函数,但是很少人知道Coalesce函数,人们会无意中使用到Coalesce函数,并且发现它比ISNULL更加强大,其实到目前为止,这个函数的确非常有用,本文主要讲解其中的一些基本使用:  首先看看联机丛书...

    sqlserver对字段出现NULL值的处理

    代码如下: -判断某些字段是否为空 –case select case when ‘字段名’ is null then ‘\N’ else convert(varchar(20),’字段名’) end as ‘NewName’ select case when null is null then ‘\N’ else convert...

    深入浅析Orcale的nvl函数和SQL Server的isnull函数

    Orcal 的 nvl函数 NVL(Expr1,Expr2)如果Expr1为NULL,...Coalesce(expr1, expr2, expr3….. exprn) 表示可以指定多个表达式的占位符。所有表达式必须是相同类型,或者可以隐性转换为相同的类型。返回第一个为NULL的值

    SQL 基础--SELECT 查询

    NULL AND F =F NULL AND T IS NULL NULL AND NULL IS NULL 就是说AND的优先级是:F ->NULL ->T OR运算: T OR T =T T OR F =T T OR NULL =T F OR T =T F OR F =F F OR NULL IS NULL ...

    SQL NULL 函数

    SQL ISNULL()、NVL()、IFNULL() 和 COALESCE() 函数 请看下面的 “Products” 表: P_Id ProductName UnitPrice UnitsInStock UnitsOnOrder 1 Jarlsberg 10.45 16 15 2 Mascarpone 32.56 23 3 Gorgonzola ...

    基于【MySQL】的【SQL核心语法】实战演练(二)

    文章目录说明必知必会全表查询全表结构查询ALTER TABLE 语句AUTO INCREMENT字段视图相关NULL相关表的NULL预处理插入NULL数据使用IS NULL筛选NULLMySQL支持的IFNULL()函数MySQL支持的COALESCE()函数再次改变表结构...

    mysql数据库的基本操作语法

    或on delete set null,前者是级联删除,后者是将从表的关联列的值设置为null。 create table student( id int auto_increment primary key, name varchar(20), classes_name varchar(20), classes_number int, /*表...

    Pro PHP and jQuery(Apress,2ed,2016)

    operator, integer division with intdiv(), the null coalesce operator, the spaceship operator, new exception types, and improvements to existing features. Pro PHP and jQuery, Second Edition is for ...

    Apress.PHP.7.Solutions.4th.Edition.rar

    David Powers has updated his definitive book to incorporate the latest techniques and changes to PHP, including the arrival of PHP 7. New features include the spaceship and null coalesce operators, ...

    hive函数大全(中文版)

    7. 空值判断: IS NULL 5 8. 非空判断: IS NOT NULL 6 9. LIKE比较: LIKE 6 10. JAVA的LIKE操作: RLIKE 6 11. REGEXP操作: REGEXP 7 二、数学运算: 7 1. 加法操作: + 7 2. 减法操作: - 7 3. 乘法操作: * 8 4. 除法...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 第一章 Oracle入门 一、 数据库概述 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,它产生于距今五十年前。...

Global site tag (gtag.js) - Google Analytics