SQL Server中奇妙的NULL

相信大家在写SQL时都会有遇到NULL的经历吧,在一个table插入NULL,与NULL作比较等等.

1.NULL意思为缺失的值(missing value).

2.三值逻辑(three-valued-logic: TRUE,FALSE,UNKNOWN). 在SQL中有三个逻辑谓词:TURE,FALSE,UNKNOWN.在大多数的编程语言中只有TRUE和FALSE,而在SQL中独有UNKNOWN,之所有存在与NULL有关.

　　比如做如下比较: NULL>32;NULL=NULL;X NULL>Y;NULL<>NULL.其计算结果均为UNKNOWN.

　　可能会有些迷惑,于二值逻辑不同(NOT TURE=FALUSE;NOT FALSE=TRUE)的是NOT UNKNOWN=UNKNOWN.

3.UNKNOWN作为FALSE时的处理. 在SQL中查询过滤时(ON,WHERE,HAVING)会把UNKNOWN作为FALSE处理,这样就不会把计算值为UNKNOWN的行添加到下一个结果集中.

4.UNKNOWN作为TRUE时的处理. 在CHECK约束中UNKNOWN却作为TRUE来处理.

　　比如在一个table中添加约束条件,约束年龄必须大于零:alter table test1 add constraint ck_age check (age>0),在插入数据时仍然可以插入NULL值(前提是这列没有定义NOT NULL约束).insert into test1(age) values(NULL)

 

代码

create table test
(
Name varchar(10),
age int
)

--add check constraint age>0
alter table test add constraint ck_age check (age>0)

insert into test values
('bluesky',null)

select * from test

 

可以插入NULL值.

 

5.再谈NULL与NULL的比较,上面已经讲过(NULL=NULL;NULL<>NULL),即NULL与NULL的比较均为UNKNOWN. 但是对于UNIOUE约束,集合操作(如UNION,EXCEPT),排序,分组时,NULL与NULL为认为是等值的.

　　5.1 如果一列有UNIQUE约束,就不能插入两个NULL值.

--add unique
alter table test add unique (Name asc)

insert into test values
(NULL,12),
(NULL,13)

先定义UNIQUE 约束,insert两个NULL时会出现如下的提示,说明NULL在unique中被当做等值处理.

　　5.2 GROUP BY会把NULL分到一个组.

代码

--drop the unique constraint
ALTER TABLE [dbo].[test] DROP CONSTRAINT [UQ__test__737584F63A81B327]

--insert two null values
insert into test values
(NULL,12),
(NULL,13)

select * from test

select Name,SUM(age)as sumage from test group by Name

 可以看到NULL被分成了一组:

 

　　5.2 ORDER BY会把NULL排序在一起.

　　5.3 在集合操作时会把NULL当成等值的来处理.

 

总之: 识别在不同的情况下:NULL操作时被作为UNKNOWN还是作为TRUE,FALSE,有助于更好的写SQL及DB设计.