转发时间： 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 值的，也考虑是否可以使用默认值取代。
Labels: Coding, Operation and Maintenance, Windows
