相信大家在寫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設計.
