摘要:NULL值
NULL是甚麼, 簡單說, NULL有兩個意義
1. 空值
2. 未知
試想一個沒有NULL值的狀況下, 建一個員工基本資料,
在這個資料表欄位中, 設計了一個到職日, 離職日的欄位,
到職日, OK, 離職日, 這就有點問題了,
員工編號 員工姓名 到職日 離職日
E001 John 2012-1-1
E002 Peter 2012-2-1
E003 Alice 2012-3-1
E004 Linda 2012-4-1
離職日要填甚麼? 因為是日期型態, 不能填空白
所以只好塞一個值, 那要塞哪個值好呢?
早期常看到會塞進一個 9999/12/31
可是這和事實並不相符, 員工並不是在 9999/12/31離職呀!
(也有人會將離職日改成文字型別來儲存, 更糟的方法)
這時候 NULL 的存在是一個非常棒的一件事, 解決了這個麻煩的問題
空值: 離職日是空的
未知: 我不知道這位員工甚麼時候離職
可是有了NULL之後, 並沒有一切太平, 一勞永逸, 有了NULL值, 又有了新的問題
1. NULL值不能做比較
Select * From Employees
Where LeaveDate = null
這樣的SQL語法, 是選不到任何資料的
因為NULL的比較必須使用 IS 關鍵字
Select * From Employees
Where LeaveDate IS null
2. NULL值會造成程式的錯誤
在程式語言裏面, 如果沒有判斷NULL值, 而將NULL值放到一般的變數
很容易發生NULL值的運算錯誤.
因此在某種程度上, NULL值的發生, 常會造成一些麻煩 需要程式去判斷,
也有人會因此而不使用NULL值, 其實這樣的思考見仁見智, 只是如果要
使用NULL值, 是需要花點時間去理解這個NULL值.
先從甚麼狀況下會發生NULL值
首先先建立一個測試資料庫
Create Database dbNull
Go
Use dbNull
Create Table Test
(
id int, name nvarchar(20),
c1 float, c2 float, c3 float
)
insert into Test(id, name, c1,c2, c3)
values(1, N'John', 55, 62, 73),
(2, N'Branda', 81, 92, null),
(3, N'Susan', 76, 66, 44),
(4, N'Kitty', 66, null, 73),
(5, N'Jeniffer', 96, 56, 84),
(6, N'Lin', null, null, null)
NULL五個發生的原因
1. Insert Into ... Values(Null,...)
Insert Into時直接寫入, NULL 值前後不要加單引號
2. t1(c1, c2, c3)
Insert Into t1(c1, c2) Values(10, 20)
c3 會自動帶入default value, 如果沒有Default Value, 會帶入NULL
資料表中有三個欄位, 在Insert Into 時, 沒有帶入第三個欄位,
3. Alter Table t1 Add c1 int
臨時新增一個欄位, 原先已有的資料, 會自動補上 NULL 值
4. Outer Join
Left Join, Right Join, Full Join
在沒有對應的資料中, 會帶入NULL值
5. 和 null 發生運算
和NULL值做 + - * / 等運算, 都會變成NULL值
例如:
Select t.id, t.name, t.c1,t.c2, t.c3, t.c1 + t.c2 + t.c3
From Test t
這個時候, t.C1 + t.C2 + t.C3 會變成 NULL
因此, 在這裡可以使用 ISNULL函數
Select t.id, t.name, t.c1,t.c2, t.c3,
isnull(t.c1,0) + isnull(t.c2,0) + isnull(t.c3, 0)
From Test t
將NULL值在運算中改為 0
但是, 在彙總函數中, (例如: SUM, MAX, MIN, CUNT, AVG) NULL值會被自動排除
Select SUM(c1), SUM(c2), SUM(c3) From TEST
Select MAX(c1), MAX(c2), MAX(c3) From TEST
Select MIN(c1), MIN(c2), MIN(c3) From TEST
Select AVG(c1), AVG(c2), AVG(c3) From TEST
Select COUNT(c1), COUNT(c2), COUNT(c3) From TEST
附帶提一點 COUNT,
COUNT(c1), COUNT(*) 的差別,
COUNT(c1) 表示 資料表中, c1不為NULL值的筆數
COUNT(*)資料表的比數(縱然是所有欄位皆為NULL, 一樣會是做一筆