本文通過(guò)各種 SQL 小例子，解釋 NULL 值的用途和帶來(lái)的問(wèn)題。
英語(yǔ)原文地址：https://mitchum.blog/null-values-in-sql-queries/
作者：MITCHUM
翻譯：高行行
參考翻譯文章：https://blog.csdn.net/lnotime/article/details/104847946
小結(jié)：
- SQL 里的 NULL 和其他編程語(yǔ)言里的 NULL 是完全不同的東西
- 在 SQL 中 NULL 為未知
翻譯水平有限，可能存在翻譯不準(zhǔn)確的地方，盡情諒解。
今天的帖子是關(guān)于 SQL 中的 NULL 值的，由我的朋友兼數(shù)據(jù)庫(kù)向?qū)?Kaley 提供。如果你想了解有關(guān) SQL，Oracle 數(shù)據(jù)庫(kù)以及使查詢運(yùn)行更快的更多信息，請(qǐng)?jiān)L問(wèn)他的網(wǎng)站。
這是一個(gè)使很多萌新開(kāi)發(fā)人員陷入困境的話題-SQL 查詢中 NULL 值的概念。
每當(dāng)你向數(shù)據(jù)庫(kù)發(fā)出SQL查詢時(shí)……你想知道一列中是否包含 NULL 值……編寫查詢以查到結(jié)果的正確方式是什么？
你應(yīng)該使用這樣的查詢嗎？
SELECT * FROM SOME_TABLE
WHERE SOME_COLUMN = NULL
要么！你應(yīng)該使用這樣的查詢嗎？
SELECT * FROM SOME_TABLE
WHERE SOME_COLUMN IS NULL
…答案是，你應(yīng)該使用第二個(gè)查詢（SOME_COLUMN IS NULL）。
下圖為實(shí)際的查詢例子 ??
[圖片上傳失敗...(image-3f58f1-1586060321607)]
[圖片上傳失敗...(image-a119ac-1586060321607)]
[圖片上傳失敗...(image-e0306a-1586060321607)]
為什么呢？
為什么其他的比較都不用 IS 關(guān)鍵字呢？
如果我們想知道一個(gè)字段是否等于 1，我們可以使用如下的 WHERE 子句：
WHERE SOME_COLUMN = 1
那么為什么我們?cè)?strong>IS關(guān)鍵字上使用 NULL 值呢？為什么我們需要區(qū)別對(duì)待 NULL ？
答案是這樣的：在 SQL 中，NULL 表示“未知”的概念 _ _（因此 NULL 值表示“未知”值）。
1. Null 為未知
在大多數(shù)數(shù)據(jù)庫(kù)中，NULL 和空字符串（由雙撇號(hào) "" 或 '' 表示）之間存在差異。
但是，并非所有數(shù)據(jù)庫(kù)都這樣：例如，Oracle 數(shù)據(jù)庫(kù)不允許你使用空字符串。任何時(shí)候 Oracle 數(shù)據(jù)庫(kù)看到一個(gè)空字符串，它都會(huì)自動(dòng)將空字符串轉(zhuǎn)換為 NULL 值。
但是，對(duì)于大多數(shù)其他數(shù)據(jù)庫(kù)，NULL 值與空字符串的處理方式不同：
- 空字符串被視為沒(méi)有值的已知值。
- 將 NULL 值視為未知值。
舉個(gè)例子，就好像問(wèn)：美國(guó)總統(tǒng)西奧多·羅斯福的中間名是什么？
- 一種答案可能是：“嗯，我不知道西奧多·羅斯福的中間名是什么?！?/strong>（此想法可以由 Theodore Roosevelt 的記錄的 MIDDLE_NAME 列中的 NULL 值表示，即中間名字段為 NULL）
- 另一種答案可能是**“西奧多·羅斯?？偨y(tǒng)實(shí)際上沒(méi)有中間名。他的父母從未給他起過(guò)中間名，我知道的事實(shí)就是西奧多·羅斯福（Theodore Roosevelt）沒(méi)有中間名。 **（你可以通過(guò)在 MIDDLE_NAME 列中輸入一個(gè)空字符串或 '' 來(lái)表示，即中間名字段為空字符串）
Oracle 數(shù)據(jù)庫(kù)是最顯著的例外，其中這兩個(gè)值實(shí)際上都將由 NULL 表示-除 Oracle 以外的大多數(shù)數(shù)據(jù)庫(kù)對(duì) NULL 和空字符串的處理方式都非常不同。
只要你記得 NULL 值代表一個(gè)未知值，那么這將有助于你編寫 SQL 查詢，并幫助你解決使用 NULL 值可能遇到的一些棘手情況。
例如，如果你要使用這樣的 WHERE 子句查詢：
SELECT * FROM SOME_TABLE
WHERE 1 = 1
該查詢將返回行（假設(shè) SOME_TABLE 不是空表?。?yàn)楸磉_(dá)式“ 1 = 1” 可證明是 true 的……它可以被證明是正確的。
如果我要說(shuō)：
SELECT * FROM SOME_TABLE
WHERE 1 = 0
然后數(shù)據(jù)庫(kù)將看到此情況，并將“ 1 = 0”評(píng)估為 false（這意味著該查詢將永遠(yuǎn)不會(huì)返回任何行）。
但是如果我要說(shuō)：
SELECT * FROM SOME_TABLE
WHERE 1 = NULL
數(shù)據(jù)庫(kù)基本上是這樣的：“我不知道這兩個(gè)值（1 和我們的黑盒 NULL 值）是否相等”……因此它不返回任何記錄。
2. 三值邏輯
當(dāng) SQL 查詢中有 WHERE 子句時(shí)，它可以具有三種不同結(jié)果之一：
- true（它將返回行）
- false（不會(huì)返回行）
- NULL（未知也不會(huì)返回行）
你可能會(huì)想，“好吧，既然數(shù)據(jù)庫(kù)對(duì)這兩個(gè)值的處理完全相同，我為什么要關(guān)心 false 和 null 之間的區(qū)別？”
好吧，讓我告訴你哪里可能遇到麻煩：讓我們介紹一下 NOT() 條件。
如果你要說(shuō)：
SELECT * FROM SOME_TABLE
WHERE NOT(1 = 1)
然后，數(shù)據(jù)庫(kù)首先要求值 1 = 1，然后說(shuō)：“好吧，那顯然是對(duì)的。”
但是隨后它將對(duì)其應(yīng)用 NOT() 條件。“當(dāng) true 被 NOT() 修飾時(shí)，它變成了 false……所以 NOT() 條件導(dǎo)致我們的 WHERE 子句在這里是 false 的?！?/p>
因此，上面的查詢不會(huì)返回任何記錄。
[圖片上傳失敗...(image-e9f5e4-1586060321607)]
但是，如果你要說(shuō)：
SELECT * FROM SOME_TABLE
WHERE NOT(1 = 0)
然后，數(shù)據(jù)庫(kù)首先計(jì)算表達(dá)式 1 = 0，并說(shuō)：“那顯然是 false 的?！?/p>
但是然后它將應(yīng)用 NOT() 條件，這將給我們相反的結(jié)果，因此它變?yōu)?true。
因此此查詢將返回記錄！
[圖片上傳失敗...(image-cf44f3-1586060321607)]
如果我發(fā)出以下查詢?cè)趺崔k？
SELECT * FROM SOME_TABLE
WHERE NOT(1 = NULL)
數(shù)據(jù)庫(kù)首先要評(píng)估 1 = NULL。（請(qǐng)記住，它將把 NULL 當(dāng)作一個(gè)未知值?。?/p>
它會(huì)說(shuō)：“我不能說(shuō) 1 是否等于 NULL，因?yàn)槲也恢?NULL（未知）值是什么?！?/p>
因此，它不會(huì)產(chǎn)生 true 的結(jié)果，也不會(huì)產(chǎn)生 false 的結(jié)果 – 而是會(huì)產(chǎn)生 NULL（未知）結(jié)果。
NULL 結(jié)果將由 NOT() 運(yùn)算符修飾。
每當(dāng)你使用 NULL 并將其置于 NOT() 條件時(shí)……結(jié)果就是另一個(gè) NULL！（未知的反面是……嗯……另一個(gè)未知）。
因此，NOT() 運(yùn)算符對(duì) null 條件不做任何事情。
所以這些查詢中的……
SELECT * FROM SOME_TABLE
WHERE NOT(1 = NULL)
SELECT * FROM SOME_TABLE
WHERE 1 = NULL
…將不返回任何記錄…即使它們是相反的！
3. NULL 和 NOT IN
如果我使用 WHERE 子句發(fā)出這樣的查詢：
SELECT * FROM SOME_TABLE
WHERE 1 IN (1, 2, 3, 4, NULL)
…那么顯然 WHERE 子句將是 true 的，由于 1 在我們的 IN 列表中，所以該查詢將返回記錄…
[圖片上傳失敗...(image-92c86b-1586060321607)]
但是如果我要說(shuō)：
SELECT * FROM SOME_TABLE
WHERE 1 NOT IN (1, 2, 3, 4, NULL)
那么顯然這將是 false 的，該查詢將永遠(yuǎn)不會(huì)返回記錄，因?yàn)閿?shù)字 1 出現(xiàn)在我們的 IN 列表中，并且我們說(shuō)“ NOT IN”…
[圖片上傳失敗...(image-ffae2d-1586060321607)]
現(xiàn)在，如果我要說(shuō)這樣的話怎么辦？
SELECT * FROM SOME_TABLE
WHERE 5 NOT IN (1, 2, 3, 4, NULL)
此 WHERE 子句將永遠(yuǎn)不會(huì)返回任何記錄，因?yàn)樗皇钦嬲目勺C明（它不能被證明是 true 的）。數(shù)字 5 沒(méi)有明確出現(xiàn)在“ IN”列表中 - 但是 5 可能在我們的“黑盒” NULL 值內(nèi)（數(shù)據(jù)庫(kù)不一定知道 NULL 的值是什么）。
這將產(chǎn)生 NULL 結(jié)果（表示未知結(jié)果），因此 WHERE 子句永遠(yuǎn)不會(huì)返回任何記錄。
[圖片上傳失敗...(image-4dd578-1586060321607)]
這就是為什么將 NULL 值等效為未知值很重要的原因 - 每當(dāng)你編寫復(fù)雜的SQL查詢時(shí)，它都會(huì)為你提供幫助。
希望你現(xiàn)在已經(jīng)準(zhǔn)備好處理 SQL 查詢中的 NULL 值！有關(guān) SQL，Oracle 數(shù)據(jù)庫(kù)以及使查詢運(yùn)行更快的更多信息，請(qǐng)?jiān)L問(wèn) blog.tuningsql.com。
資料
實(shí)際例子 ??的 SQL 文件
CREATE TABLE `user` (
`id` int(25) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int(32) DEFAULT NULL,
`sex` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
INSERT INTO `test`.`user`(`id`, `name`, `age`, `sex`, `password`) VALUES (2, '小白', 1, '0', '333');
INSERT INTO `test`.`user`(`id`, `name`, `age`, `sex`, `password`) VALUES (3, 'white', 12, '0', '111');
INSERT INTO `test`.`user`(`id`, `name`, `age`, `sex`, `password`) VALUES (4, 'white', NULL, '0', '222');
