Sql Server中的Null值
Sql Server中的Null值一、Sql Server中的Null值的理解
1、NULL 是Sql Server中的数据类型， NULL的值是未知的、是不确定的。由于 NULL 存在着无数的可能，因此两个 NULL 不是相等的关系，同样也不能说两个 NULL 就不相等，或者比较两个 NULL 的大小，这些操作都是没有意义，得不到一个确切的答案的。因此，对 NULL 的 = 、 != 、 > 、 < 、 >= 、 <= 等操作的结果 都是未知的，也就算说，这些操作的结果仍然是 NULL 。
2、SQL提供了两个谓词Is NULL和IS Not NULL来取代=null和<>null， 使用not in 谓词过滤数据时,如果 in的值中包含null,则永远不会返回值
3、当进行分组和排序时 ，认为两个NULL值是相等的，就是说GROUP BY子句会在每个组中重新组织所有的NULL值
4、OrderBY子句也会对所有的NULL值进行排序，T-SQL是把NULL值得排在了有效值之前
5、所有的聚合函数都会忽略NULL值，只有一个例外Count(*),假设一个组有两行，其QTY分别为3，null，表达式Count(*)返回2，COunt（QTY）返回1
6、判断null值语句：select * from 表 where 字段 is null;
7、转换null值语句：select 字段1，字段2，字段3，isnull(字段3，’某个值’) from 表；
二、SET ANSI_NULLS { ON | OFF } 的意义
当 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 条记录
三、变量的默认值与 null 值
命名一个变量后，如果没有给它赋初始值，它的值就是 null 。有时候需要注意初始 null 值和通过 select 语句给变量后期赋 null 的区别。因为此 ’null’ 非彼 ’null’ 。
例如
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 了。这样就把两种情况区分开了。
四、子查询中的 null
子查询中出现的 null 值经常会被我们忽视
例如
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
五、Case 语句中的 null
Case 中的 when 语句注意不要写成 when null, 否则得不到想要的结果。
下面的第 1 条 sql 错误， 2 ， 3 正确。
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