深入理解SQL中的Null
NULL 在電腦和程式設計世界中表示的是未知,不確定。雖然中文翻譯為 “空”, 但此空(null)非彼空(empty)。 Null表示的是一種未知狀態,未來狀態,例如小明兜里有多少錢我不清楚,但也不能肯定為0,這時在計算機中就使用Null來表示未知和不確定。
雖然熟練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
select * from users where deleted_at is not 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 .
如果條件調換過來, 查詢結果就沒有問題。 現在我們查詢有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 語句的方式其實很難看,而且分母被重複使用了。如果是簡單的情況還好,如果分母是個很複雜的表達式,那麼悲劇就來了: 很難讀,很難維護和修改,一不小心就是一堆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)!
以上是深入理解SQL中的Null的詳細內容。更多資訊請關注PHP中文網其他相關文章!

熱AI工具

Undresser.AI Undress
人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

Video Face Swap
使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱門文章

熱工具

記事本++7.3.1
好用且免費的程式碼編輯器

SublimeText3漢化版
中文版,非常好用

禪工作室 13.0.1
強大的PHP整合開發環境

Dreamweaver CS6
視覺化網頁開發工具

SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)

HQL和SQL在Hibernate框架中進行比較:HQL(1.物件導向語法,2.資料庫無關的查詢,3.類型安全),而SQL直接操作資料庫(1.與資料庫無關的標準,2.可執行複雜查詢和資料操作)。

《OracleSQL中除法運算的用法》在OracleSQL中,除法運算是常見的數學運算之一。在資料查詢和處理過程中,除法運算可以幫助我們計算欄位之間的比例或得出特定數值的邏輯關係。本文將介紹OracleSQL中除法運算的用法,並提供具體的程式碼範例。一、OracleSQL中除法運算的兩種方式在OracleSQL中,除法運算可以用兩種不同的方式來進行

Oracle和DB2是兩個常用的關聯式資料庫管理系統,它們都有自己獨特的SQL語法和特性。本文將針對Oracle和DB2的SQL語法進行比較與區別,並提供具體的程式碼範例。資料庫連接在Oracle中,使用以下語句連接資料庫:CONNECTusername/password@database而在DB2中,連接資料庫的語句如下:CONNECTTOdataba

MyBatis動態SQL標籤解讀:Set標籤用法詳解MyBatis是一個優秀的持久層框架,它提供了豐富的動態SQL標籤,可以靈活地建構資料庫操作語句。其中,Set標籤是用來產生UPDATE語句中SET子句的標籤,在更新作業中非常常用。本文將詳細解讀MyBatis中Set標籤的用法,以及透過具體的程式碼範例來示範其功能。什麼是Set標籤Set標籤用於MyBati

SQL中的Identity是什麼,需要具體程式碼範例在SQL中,Identity是一種用於產生自增數字的特殊資料類型,它常用於唯一識別表中的每一行資料。 Identity欄位通常與主鍵列搭配使用,可確保每筆記錄都有獨一無二的識別碼。本文將詳細介紹Identity的使用方式以及一些實際的程式碼範例。 Identity的基本使用方式在建立表格時,可以使用Identit

解決方法:1、檢查登入使用者是否具有足夠的權限來存取或操作該資料庫,確保該使用者俱有正確的權限;2、檢查SQL Server服務的帳戶是否具有存取指定檔案或資料夾的權限,確保該帳戶具有足夠的權限來讀取和寫入該文件或資料夾;3、檢查指定的資料庫文件是否已被其他進程打開或鎖定,嘗試關閉或釋放該文件,並重新運行查詢;4、嘗試以管理員身份運行Management Studio等等。

資料庫技術大比拼:Oracle和SQL的差別有哪些?在資料庫領域中,Oracle和SQLServer是兩種備受推崇的關聯式資料庫管理系統。儘管它們都屬於關係型資料庫的範疇,但兩者之間存在著許多不同之處。在本文中,我們將深入探討Oracle和SQLServer之間的區別,以及它們在實際應用中的特徵和優勢。首先,Oracle和SQLServer在語法方面存

SQL中MONTHS_BETWEEN是一個常見的函數,用於計算兩個日期之間的月份差。它的使用方法取決於特定的資料庫管理系統。
