在查詢數據庫時,如果你想知道一個列是否為 NULL,SQL 查詢語句該怎麼寫呢?
是不是這樣:
<code>SELECT * FROM SOME_TABLEWHERE SOME_COLUMN IS NULL/<code>
或者這樣寫:
複製代碼
<code>WHERE SOME_COLUMN = 1/<code>
正確的寫法應該是第二種(WHERE SOME_COLUMN IS NULL)。
為什麼要這樣寫?
在進行數據庫數據比較操作時,我們不會使用“IS”關鍵詞,不是嗎?
例如,如果我們想要知道一個列的值是否等於 1,WHERE 語句是這樣的:
複製代碼
<code>WHERE SOME_COLUMN = 1/<code>
那為什麼 NULL 值要用 IS 關鍵字呢?為什麼要以這種方式來處理 NULL?
因為,在 SQL 中,NULL 表示“未知”。也就是說,NULL 值表示的是“未知”的值。
NULL 等於未知
在大多數數據庫中,NULl 和空字符串是有區別的。
但並不是所有數據庫都這樣,例如,Oracle 就不支持空字符串,它會把空字符串自動轉成 NULL 值。
在其他大多數數據庫裡,NULL 值和字符串的處理方式是不一樣的:
- 空字符串表示“沒有值”,這個值是已知的。
- NULL 表示“未知值”,這個值是未知的。
這就好比我問了一個問題:“美國總統羅斯福的中間名是什麼”?
- 可能有人會回答說:“我不知道羅斯福總統的中間名是什麼”。對於這種情況,可以在數據庫中使用 MIDDLE_NAME 列來表示羅斯福的中間名,而這一列的值為 NULL。
- 也可能有人會回答說:“羅斯福總統沒有中間名。他的父母沒有給他取中間名,所以我知道羅斯福總統確實沒有中間名”。對於這種情況,MIDDLE_NAME 列應該是一個空字符串。
Oracle 比較特殊,兩個值都使用 NULL 來表示,而其他大多數數據庫會區分對待。
但只要記住 NULL 表示的是一個未知的值,那麼在寫 SQL 查詢語句時就會得心應手。
例如,如果你有一個這樣的查詢語句:
複製代碼
<code>SELECT * FROM SOME_TABLEWHERE 1 = 1/<code>
這個查詢會返回所有的行(假設 SOME_TABLE 不是空表),因為表達式“1=1”一定為 true。
如果我這樣寫:
複製代碼
<code>SELECT * FROM SOME_TABLEWHERE 1 = 0/<code>
表達式“1=0”是 false,這個查詢語句不會返回任何數據。
但如果我寫成這樣:
複製代碼
<code>SELECT * FROM SOME_TABLEWHERE 1 = NULL/<code>
這個時候,數據庫不知道這兩個值(1 和 NULL)是否相等,所以它也不會返回任何數據。
三元邏輯
SQL 查詢語句中的 WHERE 一般會有三種結果:
- 它可以是 true(這個時候會返回數據);
- 它可以是 false(這個時候不會返回數據);
- 它也可以是 NULL 或未知(這個時候也不會返回數據)。
你可能會想:“既然這樣,那我為什麼要去關心是 false 還是 NULL?它們不是都不會返回數據嗎?”
接下來,我來告訴你在哪些情況下會有問題:我們來看看 NOT()。
假設有這樣的一個查詢語句:
複製代碼
<code>SELECT * FROM SOME_TABLEWHERE NOT(1 = 1)/<code>
數據庫首先會計算 1=1,這個顯然是 true。
接著,數據庫會應用 NOT() 條件,所以 WHERE 返回 false。
所以,上面的查詢不會返回任何數據。
但如果把語句改成這樣:
複製代碼
<code>SELECT * FROM SOME_TABLEWHERE NOT(1 = 0)/<code>
數據庫首先會計算 1=0,這個肯定是 false。
接著,數據庫應用 NOT() 條件,這樣就得到相反的結果,變成了 true。
所以,這個語句會返回數據。
但如果把語句再改成下面這樣呢?
複製代碼
<code>SELECT * FROM SOME_TABLEWHERE NOT(1 = NULL)/<code>
數據庫首先計算 1=NULL,它不知道 1 是否等於 NULL,因為它不知道 NULL 的值是什麼。
所以,這個計算不會返回 true,也不會返回 false,它會返回一個 NULL。
接下來,NOT() 會繼續解析上一個計算返回的結果。
當 NOT() 遇到 NULL,它會生成另一個 NULL。未知的相反面是另一個未知。
所以,對於這兩個查詢:
複製代碼
<code>SELECT * FROM SOME_TABLEWHERE NOT(1 = NULL)/<code>
和
複製代碼
<code>SELECT * FROM SOME_TABLEWHERE 1 = NULL/<code>
都不會返回數據,儘管它們是完全相反的。
NULL 和 NOT IN
如果我有這樣的一個查詢語句:
複製代碼
<code>SELECT * FROM SOME_TABLEWHERE 1 IN (1, 2, 3, 4, NULL)/<code>
很顯然,WHERE 返回 true,這個語句將返回數據,因為 1 在括號列表裡是存在的。
但如果這麼寫:
複製代碼
<code>SELECT * FROM SOME_TABLEWHERE 1 NOT IN (1, 2, 3, 4, NULL)/<code>
很顯然,WHERE 返回 false,這個查詢不會返回數據,因為 1 在括號列表裡存在,但我們說的是“NOT IN”。
但如果我們把語句改成這樣呢?
複製代碼
<code>SELECT * FROM SOME_TABLEWHERE 5 NOT IN (1, 2, 3, 4, NULL)/<code>
這裡的 WHRE 不會返回數據,因為它的結果不是 true。數字 5 在括號列表裡可能不存在,也可能存在,因為當中有一個 NULL 值(數據庫不知道 NULL 的值是什麼)。
這個 WHERE 會返回 NULL,所以整個查詢不會返回任何數據。
希望你們現在都清楚該怎麼在 SQL 語句中處理 NULL 值了。
英文原文
Null Values in SQL Queries
中文譯文:https://www.infoq.cn/article/CKm0rLd6VZaSxj6iR1ib
閱讀更多 架構師筆記 的文章