SQL Server中的Null值解析

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 值的，也考虑是否可以使用默认值取代。