雖然熟練掌握SQL的人對(duì)于Null不會(huì)有什么疑問(wèn)，但總結(jié)得很全的文章還是很難找，看到一篇英文版的, 感覺(jué)還不錯(cuò)。

Tony Hoare 在1965年發(fā)明了 null 引用, 并認(rèn)為這是他犯下的“幾十億美元的錯(cuò)誤”. 即便是50年后的今天, SQL中的 null 值還是導(dǎo)致許多常見(jiàn)錯(cuò)誤的罪魁禍?zhǔn)?

我們一起來(lái)看那些最令人震驚的情況。

Null不支持大小/相等判斷

下面的2個(gè)查詢,不管表 users 中有多少條記錄，返回的記錄都是0行:
 

select * from users where deleted_at = null;
 
– result: 0 rows
 
select * from users where deleted_at != null;
 
– result: 0 rows

怎么會(huì)這樣子? 一切只因?yàn)?null 是表示一種“未知”的類型。也就是說(shuō)，用常規(guī)的比較操作符(normal conditional operators)來(lái)將 null 與其他值比較是沒(méi)有意義的。 Null 也不等于 Null(近似理解: 未知的值不能等于未知的值，兩者間的關(guān)系也是未知，否則數(shù)學(xué)和邏輯上就亂套了)。

– 注意: 下面的SQL適合于MySQL,如果是Oracle，你需要加上 … from dual;

select null > 0;
 
– result: null
 
select null < 0;
 
– result: null
 
select null = 0;
 
– result: null
 
select null = null;
 
– result: null
 
select null != null;
 
– result: null

將某個(gè)值與 null 進(jìn)行比較的正確方法是使用 is 關(guān)鍵字, 以及 is not 操作符:
 

select * from users
 
where deleted_at is null;
 
– result: 所有被標(biāo)記為刪除的 users

如果想要判斷兩列的值是否不相同，則可以使用 is distinct from:
 

select * from users
 
where has_address is distinct from has_photo
 
– result: 地址(address)或照片(photo)兩者只有其一的用戶

not in 與 Null

子查詢(subselect)是一種很方便的過(guò)濾數(shù)據(jù)的方法。例如,如果想要查詢沒(méi)有任何包的用戶,可以編寫(xiě)下面這樣一個(gè)查詢:
 

select * from users 
 
where id not in (select user_id from packages)

但此時(shí)假若 packages 表中某一行的 user_id 是 null 的話，問(wèn)題就來(lái)了: 返回結(jié)果是空的! 要理解為什么會(huì)發(fā)生這種古怪的事情, 我們需要理解SQL編譯器究竟干了些什么. 下面是一個(gè)更簡(jiǎn)單的示例:
 

select * from users 
 
where id not in (1, 2, null)

這個(gè)SQL語(yǔ)句會(huì)被轉(zhuǎn)換為:
 

select * from users 
 
where id != 1 and id != 2 and id != null

我們知道，id != null 結(jié)果是個(gè)未知值, null. 而任意值和 null 進(jìn)行 and 運(yùn)算的結(jié)果都是 null, 所以相當(dāng)于沒(méi)有其他條件. 那么出這種結(jié)果的原因就是 null 的邏輯值不為 true.

如果條件調(diào)換過(guò)來(lái), 查詢結(jié)果就沒(méi)有問(wèn)題。 現(xiàn)在我們查詢有package的用戶.
 

select * from users 
 
where id in (select user_id from packages)

同樣我們可以使用簡(jiǎn)單的例子:
 

select * from users
 
where id in (1, 2, null)

這條SQL被轉(zhuǎn)換為:
 

select * from users 
 
where id = 1 or id = 2 or id = null

因?yàn)?where 子句中是一串的 or 條件，所以其中某個(gè)的結(jié)果為 null 也是無(wú)關(guān)緊要的。非真(non-true)值并不影響子句中其他部分的計(jì)算結(jié)果,相當(dāng)于被忽略了。

Null與排序

在排序時(shí), null 值被認(rèn)為是最大的. 在降序排序時(shí)(descending)這會(huì)讓你非常頭大,因?yàn)?null值排在了最前面。

下面這個(gè)查詢是為了根據(jù)得分顯示用戶排名, 但它將沒(méi)有得分的用戶排到了最前面!
 

select name, points
 
from users
 
order by 2 desc;
 
– points 為 null 的記錄排在所有記錄之前!

解決這類問(wèn)題有兩種思路。最簡(jiǎn)單的一種是用 coalesce 消除 null的影響:
 

– 在輸出時(shí)將 null 轉(zhuǎn)換為 0 :
 
select name, coalesce(points, 0)
 
from users
 
order by 2 desc;
 
– 輸出時(shí)保留 null, 但排序時(shí)轉(zhuǎn)換為 0 :
 
select name, points
 
from users
 
order by coalesce(points, 0) desc;

還有一種方式需要數(shù)據(jù)庫(kù)的支持，指定排序時(shí)將 null 值放在最前面還是最后面:
 

select name, coalesce(points, 0)
 
from users
 
order by 2 desc nulls last;

當(dāng)然， null 也可以用來(lái)防止錯(cuò)誤的發(fā)生，比如處理除數(shù)為0的數(shù)學(xué)運(yùn)算錯(cuò)誤。

被 0 除

除數(shù)為0是一個(gè)非常 egg-painfull 的錯(cuò)誤。昨天還運(yùn)行得好好的SQL，突然被0除一下子就出錯(cuò)了。一個(gè)常用的解決方法是先用 case 語(yǔ)句判斷分母(denominator)是否為0，再進(jìn)行除法運(yùn)算。

select case when num_users = 0 then 0 
 
else total_sales/num_users end;

ase 語(yǔ)句的方式其實(shí)很難看，而且分母被重復(fù)使用了。如果是簡(jiǎn)單的情況還好，如果分母是個(gè)很復(fù)雜的表達(dá)式，那么悲劇就來(lái)了： 很難讀，很難維護(hù)和修改,一不小心就是一堆BUG.

這時(shí)候我們可以看看 null 的好處. 使用 nullif 使得分母為0時(shí)變成 null. 這樣就不再報(bào)錯(cuò), num_users = 0 時(shí)返回結(jié)果變?yōu)?null.
 

select total_sales/nullif(num_users, 0);

 
nullif 是將其他值轉(zhuǎn)為 null, 而Oracle的 nvl 是將 null 轉(zhuǎn)換為其他值。

如果不想要 null，而是希望轉(zhuǎn)換為 0 或者其他數(shù)， 則可以在前一個(gè)SQL的基礎(chǔ)上使用 coalesce函數(shù):
 

select coalesce(total_sales/nullif(num_users, 0), 0);

 
null 再轉(zhuǎn)換回0

Conclusion

Tony Hoare 也許會(huì)后悔自己的錯(cuò)誤, 但至少 null 存在的問(wèn)題很容易地就解決了. 那么快去練練新的大招吧，從此遠(yuǎn)離 null 挖出來(lái)的無(wú)效大坑(nullifying)!

最新評(píng)論