相信大家在写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)
代码
createtabletest(Namevarchar(10),ageint)--add check constraint age>0altertabletestaddconstraintck_agecheck(age>0)insertintotestvalues('bluesky',null)select*fromtest
可以插入NULL值.
5.再谈NULL与NULL的比较,上面已经讲过(NULL=NULL;NULL<>NULL),即NULL与NULL的比较均为UNKNOWN. 但是对于UNIOUE约束,集合操作(如UNION,EXCEPT),排序,分组时,NULL与NULL为认为是等值的.
5.1 如果一列有UNIQUE约束,就不能插入两个NULL值.
--add uniquealter 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 constraintALTER TABLE [dbo].[test] DROP CONSTRAINT [UQ__test__737584F63A81B327]--insert two null valuesinsert into test values(NULL,12),(NULL,13)select * from testselect 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设计.