詳解數據庫語言中的null值

2024-07-09 22:47:45

字體：大中小

來源：轉載

供稿：網友

雖然熟練掌握SQL的人對于Null不會有什么疑問，但總結得很全的文章還是很難找，看到一篇英文版的, 感覺還不錯。

Tony Hoare 在1965年發明了 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 是表示一種“未知”的類型。也就是說，用常規的比較操作符(normal conditional operators)來將 null 與其他值比較是沒有意義的。 Null 也不等于 Null(近似理解: 未知的值不能等于未知的值，兩者間的關系也是未知，否則數學和邏輯上就亂套了)。

– 注意: 下面的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

將某個值與 null 進行比較的正確方法是使用 is 關鍵字, 以及 is not 操作符:

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)是一種很方便的過濾數據的方法。例如,如果想要查詢沒有任何包的用戶,可以編寫下面這樣一個查詢:

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.