转：SQL Server中的Null值解析

转发时间： 2011-12-25 20:16 from: http://blog.csdn.net/cassaba/article/details/4748528 NULL 是数据库中特有的数据类型，当一条记录的某个列为 NULL ，则表示这个列的值是未知的、是不确定的。既然是未知的，就有无数种的可能性。因此， NULL 并不是一个确定的值。 这是 NULL 的由来、也是 NULL 的基础，所有和 NULL 相关的操作的结果都可以从 NULL 的概念推导出来。 判断一个字段是否为 NULL ， 应该用 IS NULL 或 IS NOT NULL ，而不能用 ‘=’ 。对 NULL 的判断只能定性，既是不是 NULL （ IS NULL/IS NOT NULL ），而不能定值。简单的说，由于 NULL 存在着无数的可能，因此两个 NULL 不是相等的关系，同样也不能说两个 NULL 就不相等，或者比较两个 NULL 的大小，这些操作都是没有意义，得不到一个确切的答案的。因此，对 NULL 的 = 、 != 、 > 、 < 、 >= 、 <= 等操作的结果 都是未知的，也就算说，这些操作的结果仍然是 NULL 。 同理，对 NULL 进行 + 、 - 、 * 、 / 等操作的结果也是未知的，所以也是 NULL 。 所以，很多时候会这样总结 NULL ，除了 IS NULL 、 IS NOT NULL 以外，对 NULL 的任何操作的结果还是 NULL 。 下面列举了 SQL Server 中处理 null 值时容易忽略的几个地方。 在阅读下文之前，请先创建示例表， Script 如下： create table dbo. cassaba_null ( column1 nvarchar ( 50) not null, column2 nvarchar ( 50) null ) go insert into dbo. cassaba_null values ( '1' , null) insert into dbo. cassaba_null values ( '2' , 'string' ) insert into dbo. cassaba_null values ( '3' , '' ) go 1. 使用 =null / <>null 咦，开篇不就说不能使用 =null / <> null 来判断 null 值吗？是的，默认情况下的确如此。实际上 SQL Server 可以 使用 SET ANSI_NULLS { ON | OFF } 设定来控制 =null / <>null 的行为。 当 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。 即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行。 但是当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵守 ISO 标准。使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL 的行。 参看下面演示代码： 示例 1 ： set ansi_nulls on declare @test1 nvarchar ( 10) if ( @test1 = null) select 1 else select 2 --------------------------------------------------------------------- 结果返回 2 示例 2 ： set ansi_nulls off declare @test1 nvarchar ( 10) if ( @test1 = null) select 1 else select 2 --------------------------------------------------------------------- 结果返回 1 示例 3 ： set ansi_nulls on select * from dbo. cassaba_null where column2 != null --------------------------------------------------------------------- 无记录返回 示例 4 ： set ansi_nulls off select * from dbo. cassaba_null where column2 != null --------------------------------------------------------------------- 返回第 2 ， 3 条记录 2. 改变 null 值的连接行为 SQL Server 提供 SET CONCAT_NULL_YIELDS_NULL { ON | OFF } 来控制 null 与其它字符串连接的行为。 当 SET CONCAT_NULL_YIELDS_NULL 为 ON 时，串联空值与字符串将产生 NULL 结果。例如， SELECT 'abc' + NULL 将生成 NULL 。 当 SET CONCAT_NULL_YIELDS_NULL 为 OFF 时，串联空值与字符串将产生字符串本身（空值作为空字符串处理）。例如， SELECT 'abc' + NULL 将生成 abc 。 如果未指定 SET CONCAT_NULL_YIELDS ，则应用 CONCAT_NULL_YIELDS_NULL 数据库选项的设置。 注：在 SQL Server 的未来版本中， CONCAT_NULL_YIELDS_NULL 将始终为 ON ，而且将该选项显式设置为 OFF 的任何应用程序都将产生一个错误。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。 如果不希望每次都判断 null, 可以使用 isnull 函数来达到每次把 null 自动替换为空字符串进行连接的效果。 示例 5 ： declare @test nvarchar ( 50) select isnull ( @test, '' ) + 'extend' go select column1 + isnull ( column2, '' ) as column3 from dbo. cassaba_null go --------------------------------------------------------------------- 3. 变量的默认值与 null 值 命名一个变量后，如果没有给它赋初始值，它的值就是 null 。有时候需要注意初始 null 值和通过 select 语句给变量后期赋 null 的区别。因为此 ’null’ 非彼 ’null’ 。 示例 6 ： declare @test nvarchar ( 50) -- 无符合条件的语句 , 保持默认值 null select @test= column2 from dbo. cassaba_null where column1 = '4' -- 有符合条件的语句，返回的栏位值为 null ，并且赋给 @test select @test= column2 from dbo. cassaba_null where column1 = '1' 如果后面的代码使用 @test 的值是否为 null 来判断有没有匹配的记录，则可能发生错误。碰到这种状况，我们同样可以使用 isnull 函数来避免这个问题。 select @test= isnull ( column2, '' ) from dbo. cassaba_null where column1 = '1' 如上面的语句，即使有匹配的记录返回 null, 也会变成空字符串赋给 @test 了。这样就把两种情况区分开了。 4. 子查询中的 null 子查询中出现的 null 值经常会被我们忽视，先查看下面的例子。 示例 7 ： set ansi_nulls off select * from cassaba_null a where a. column2 = ( select b. column2 from dbo. cassaba_null b where b. column1 = 1) --------------------------------------------------------------------- 不管上面 ansi_nulls 设置为 on 还是 off ，始终没有记录返回。我们修改一下查询语句： select * from cassaba_null a where a. column2 in ( select b. column2 from dbo. cassaba_null b where b. column1 = 1) 这样，如果 ansi_nulls 设置为 on ， 则没有记录返回。 如果设置为 off ，则会返回一条记录。 对于这种状况，如果我们确定不需要返回 null 值记录，则使用下面的 sql 语句： select * from cassaba_null a where a. column2 in( select b. column2 from dbo. cassaba_null b where b. column1 = 1 and b. column2 is not null) 反之，使用下面的语句： select * from cassaba_null a where a. column2 in( select b. column2 from dbo. cassaba_null b where b. column1 = 1 and b. column2 is not null) or a. column2 is null 5. Case 语句中的 null Case 中的 when 语句注意不要写成 when null, 否则得不到想要的结果。 下面的第 1 条 sql 错误， 2 ， 3 正确。 示例 8 ： select case column2 when null then 'a' else 'b' end as column3 from cassaba_null select case isnull ( column2, 'a' ) when 'a' then 'a' else 'b' end as column3 from cassaba_null select case when column2 is null then 'a' else 'b' end as column3 from cassaba_null 6. 与 null 相关的函数 表 8-4 NULL 函数 函 数 名 描　　述 ISNULL ISNULL 检测表达式是否为 NULL ，如果是的话替换 NULL 值为另外一个值 COALESCE COALESCE 函数返回指定表达式列表的第一个非 NULL 值 NULLIF 当指定的两个表达式有相同值的时候 NULLIF 返回 NULL 值，否则返回第一个表达式的值 具体的使用方法，请参考 SQL Server 的帮助文档。 总而言之，在写 sql 的时候，我们要非常注意 null 值的检查和使用。同时，在设计数据库的时候，如果栏位不可能为 null 值，就应该添加 not null 的约束，对于可能为 null 值的，也考虑是否可以使用默认值取代。