? ? ? ? NULL 在計算機和NULL世界中表示的是未知,不確定。雖然中文翻譯為 “空”, 但此空(null)非彼空(empty)。 Null表示的是一種未知狀態，未來狀態,比如小明兜里有多少錢我不清楚，但也不能肯定為0，這時在計算機中就使用Null來表示未知和不確定。
雖然熟練掌握sql的人對于null不會有什么疑問，但總結得很全的文章還是很難找，看到一篇英文版的, 感覺還不錯。
Tony Hoare 在1965年發明了 null NULL, 并認為這是他犯下的“幾十億美元的錯誤”. 即便是50年后的今天, SQL中的 null 值還是導致許多常見錯誤的罪魁禍首.
我們一起來看那些最令人震驚的情況。
Null不支持大小/相等判斷
下面的2個查詢,不管表 users 中有多少條記錄，返回的記錄都是0行:
select?*?from?users?where?deleted_at?=?null; ? –?result:?0?rows ? select?*?from?users?where?deleted_at?!=?null; ? –?result:?0?rows
怎么會這樣子? 一切只因為 null 是表示一種“未知”的類型。也就是說，用常規的比較NULL(normal conditional operators)來將 null 與其他值比較是沒有意義的。 Null 也不等于 Null(近似理解: 未知的值不能等于未知的值，兩者間的關系也是未知，否則數學和邏輯上就亂套了)。
– 注意: 下面的SQL適合于NULL,如果是Oracle，你需要加上 … from dual;
select?null?>?0; ? –?result:?null ? select?null?<p> 將某個值與 null 進行比較的正確方法是使用 is 關鍵字, 以及 is not 操作符:</p><pre class="brush:sql;toolbar:false">select?*?from?users ? where?deleted_at?is?null; ? –?result:?所有被標記為刪除的?users
如果想要判斷兩列的值是否不相同，則可以使用 is distinct from:
select?*?from?users ? where?has_address?is?distinct?from?has_photo ? –?result:?地址(address)或照片(photo)兩者只有其一的用戶
not in 與 Null
子查詢(subselect)是一種很方便的NULL的方法。例如,如果想要查詢沒有任何包的用戶,可以編寫下面這樣一個查詢:
select?*?from?users? ? where?id?not?in?(select?user_id?from?packages)
但此時假若 packages 表中某一行的 user_id 是 null 的話，問題就來了: 返回結果是空的! 要理解為什么會發生這種古怪的事情, 我們需要理解SQL編譯器究竟干了些什么. 下面是一個更簡單的示例:
select?*?from?users? ? where?id?not?in?(1,?2,?null)
這個SQL語句會被轉換為:
select?*?from?users? ? where?id?!=?1?and?id?!=?2?and?id?!=?null
我們知道，id != null 結果是個未知值, null. 而任意值和 null 進行 and 運算的結果都是 null, 所以相當于沒有其他條件. 那么出這種結果的原因就是 null 的邏輯值不為 true.
如果條件調換過來, 查詢結果就沒有問題。 現在我們查詢有package的用戶.
select?*?from?users? ? where?id?in?(select?user_id?from?packages)
同樣我們可以使用簡單的例子:
select?*?from?users ? where?id?in?(1,?2,?null)
這條SQL被轉換為:
select?*?from?users? ? where?id?=?1?or?id?=?2?or?id?=?null
因為 where 子句中是一串的 or 條件，所以其中某個的結果為 null 也是無關緊要的。非真(non-true)值并不影響子句中其他部分的計算結果,相當于被忽略了。
Null與排序
在排序時, null 值被認為是最大的. 在降序排序時(descending)這會讓你非常頭大,因為 null值排在了最前面。
下面這個查詢是為了根據得分顯示用戶排名, 但它將沒有得分的用戶排到了最前面!
select?name,?points ? from?users ? order?by?2?desc; ? –?points?為?null?的記錄排在所有記錄之前!
解決這類問題有兩種思路。最簡單的一種是用 coalesce 消除 null的影響:
–?在輸出時將?null?轉換為?0?: ? select?name,?coalesce(points,?0) ? from?users ? order?by?2?desc; ? –?輸出時保留?null,?但排序時轉換為?0?: ? select?name,?points ? from?users ? order?by?coalesce(points,?0)?desc;
還有一種方式需要數據庫的支持，指定排序時將 null 值放在最前面還是最后面:
select?name,?coalesce(points,?0) ? from?users ? order?by?2?desc?nulls?last;
當然， null 也可以用來防止錯誤的發生，比如處理除數為0的數學運算錯誤。
被 0 除
除數為0是一個非常 egg-painfull 的錯誤。昨天還運行得好好的SQL，突然被0除一下子就出錯了。一個常用的解決方法是先用 case 語句判斷分母(denominator)是否為0，再進行除法運算。
select?case?when?num_users?=?0?then?0? ? else?total_sales/num_users?end;
ase 語句的方式其實很難看，而且分母被重復使用了。如果是簡單的情況還好，如果分母是個很復雜的NULL，那么悲劇就來了： 很難讀，很難維護和修改,一不小心就是一堆BUG.
這時候我們可以看看 null 的好處. 使用 nullif 使得分母為0時變成 null. 這樣就不再報錯, num_users = 0 時返回結果變為 null.
select?total_sales/nullif(num_users,?0); ? nullif?是將其他值轉為?null,?而Oracle的?nvl?是將?null?轉換為其他值。
如果不想要 null，而是希望轉換為 0 或者其他數， 則可以在前一個SQL的基礎上使用 coalesce函數:
select?coalesce(total_sales/nullif(num_users,?0),?0); ? null?再轉換回0
Conclusion
Tony Hoare 也許會后悔自己的錯誤, 但至少 null 存在的問題很容易地就解決了. 那么快去練練新的大招吧，從此遠離 null 挖出來的無效大坑(nullifying)!
