首頁 資料庫 mysql教程 深入理解SQL中的Null

深入理解SQL中的Null

May 01, 2017 pm 01:43 PM

  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中文網其他相關文章!

本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

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

AI Clothes Remover

AI Clothes Remover

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

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

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

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

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

Hibernate 框架中 HQL 和 SQL 的差異是什麼? Hibernate 框架中 HQL 和 SQL 的差異是什麼? Apr 17, 2024 pm 02:57 PM

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

Oracle SQL中除法運算的用法 Oracle SQL中除法運算的用法 Mar 10, 2024 pm 03:06 PM

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

Oracle與DB2的SQL語法比較與區別 Oracle與DB2的SQL語法比較與區別 Mar 11, 2024 pm 12:09 PM

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

詳解MyBatis動態SQL標籤中的Set標籤功能 詳解MyBatis動態SQL標籤中的Set標籤功能 Feb 26, 2024 pm 07:48 PM

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

SQL中的identity屬性是什麼意思? SQL中的identity屬性是什麼意思? Feb 19, 2024 am 11:24 AM

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

SQL出現5120錯誤怎麼解決 SQL出現5120錯誤怎麼解決 Mar 06, 2024 pm 04:33 PM

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

資料庫技術大比拼:Oracle和SQL的差別有哪些? 資料庫技術大比拼:Oracle和SQL的差別有哪些? Mar 09, 2024 am 08:30 AM

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

SQL中months_between如何使用 SQL中months_between如何使用 Jan 25, 2024 pm 03:23 PM

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

See all articles