SQL Server中奇妙的NULL

 http://www.cnblogs.com/changbluesky/archive/2010/05/07/1729519.html

ps.懶得寫，懶得總結，copy一個總結不錯的，不過還有一些沒說到，總之，翻過幾次錯遇到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設計.