您的位置:首页 > 数据库 > 数据库管理

Sql Server中的Null值

更多 2014/11/13 来源:数据库管理浏览量:2058
学习标签: Sql Server
本文导读:NULL 是Sql Server中的数据类型,当一条记录的某个列为 NULL ,则表示这个列的值是未知的、是不确定的。因此, NULL 并不是一个确定的值。判断一个字段是否为 NULL , 应该用 IS NULL 或 IS NOT 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 的行

例如

 
SQL 代码   复制

示例 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

---------------------------------------------------------------------

返回第 23 条记录

 

三、变量的默认值与 null 值

命名一个变量后,如果没有给它赋初始值,它的值就是 null 。有时候需要注意初始 null 值和通过 select 语句给变量后期赋 null 的区别。因为此 ’null’ 非彼 ’null’ 。

例如

 
SQL 代码   复制

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 值经常会被我们忽视

例如

 
SQL 代码   复制

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 正确。

 
SQL 代码   复制

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

 

 

收藏
359
很赞
399