[[281624]]

NULL

NULL 用于表示缺失的值或遺漏的未知數(shù)據(jù)，不是某種具體類(lèi)型的值。數(shù)據(jù)表中的 NULL 值表示該值所處的字段為空，值為 NULL 的字段沒(méi)有值，尤其要明白的是：NULL 值與 0 或者空字符串是不同的。

兩種NULL

這種說(shuō)法大家可能會(huì)覺(jué)得很奇怪，因?yàn)?SQL 里只存在一種 NULL 。然而在討論 NULL 時(shí)，我們一般都會(huì)將它分成兩種類(lèi)型來(lái)思考：“未知”(unknown)和“不適用”(not applicable,inapplicable)。

以“不知道戴墨鏡的人眼睛是什么顏色”這種情況為例，這個(gè)人的眼睛肯定是有顏色的，但是如果他不摘掉眼鏡，別人就不知道他的眼睛是什么顏色。這就叫作未知。

而“不知道冰箱的眼睛是什么顏色”則屬于“不適用”。因?yàn)楸涓揪蜎](méi)有眼睛，所以“眼睛的顏色”這一屬性并不適用于冰箱。“冰箱的眼睛的顏色”這種說(shuō)法和“圓的體積”“男性的分娩次數(shù)”一樣，都是沒(méi)有意義的。

平時(shí)，我們習(xí)慣了說(shuō)“不知道”，但是“不知道”也分很多種。“不適用”這種情況下的 NULL ，在語(yǔ)義上更接近于“無(wú)意義”，而不是“不確定”。

這里總結(jié)一下：“未知”指的是“雖然現(xiàn)在不知道，但加上某些條件后就可以知道”;而“不適用”指的是“無(wú)論怎么努力都無(wú)法知道”。

關(guān)系模型的發(fā)明者 E.F. Codd 最先給出了這種分類(lèi)。下圖是他對(duì)“丟失的信息”的分類(lèi)。

“IS NULL”而非“= NULL”?

為什么必須寫(xiě)成“IS NULL”，而不是“= NULL”?我相信不少人有這樣的困惑吧，尤其是相信剛學(xué) SQL 的小伙伴。我們來(lái)看個(gè)具體的案例，假設(shè)我們有如下表以及數(shù)據(jù)：

DROP TABLE IF EXISTS t_sample_null; 
CREATE TABLE t_sample_null ( 
    id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', 
    name VARCHAR(50) NOT NULL COMMENT '名稱(chēng)', 
    remark VARCHAR(500) COMMENT '備注', 
    primary key(id) 
) COMMENT 'NULL樣例'; 
 
INSERT INTO t_sample_null(name, remark) 
VALUES('zhangsan', '張三'),('李四', NULL); 

我們要查詢(xún)備注為 NULL 的記錄(為 NULL 這種叫法本身是不對(duì)的，只是我們?nèi)粘Ｖ幸呀?jīng)叫習(xí)慣了，具體往下看)，怎么查，很多新手會(huì)寫(xiě)出這樣的 SQL：

-- SQL 不報(bào)錯(cuò)，但查不出結(jié)果 
SELECT * FROM t_sample_null WHERE remark = NULL; 

執(zhí)行時(shí)不報(bào)錯(cuò)，但是查不出我們想要的結(jié)果， 這是為什么 ?這個(gè)問(wèn)題我們先放著，我們往下看。

三值邏輯

這個(gè)三值邏輯不是三目運(yùn)算，指的是三個(gè)邏輯值，有人可能有疑問(wèn)了，邏輯值不是只有真(true)和假(false)嗎，哪來(lái)的第三個(gè)?

說(shuō)這話(huà)時(shí)我們需要注意所處的環(huán)境，在主流的編程語(yǔ)言中(C、JAVA、Python、JS等)中，邏輯值確實(shí)只有 2 個(gè)，但在 SQL 中卻存在第三個(gè)邏輯值：unknown。這有點(diǎn)類(lèi)似于我們平時(shí)所說(shuō)的：對(duì)、錯(cuò)、不知道。

邏輯值 unknown 和作為 NULL 的一種的 UNKNOWN (未知)是不同的東西。前者是明確的布爾型的邏輯值，后者既不是值也不是變量。

為了便于區(qū)分，前者采用小寫(xiě)字母 unknown ，后者用大寫(xiě)字母 UNKNOWN 來(lái)表示。為了讓大家理解兩者的不同，我們來(lái)看一個(gè) x=x 這樣的簡(jiǎn)單等式。x 是邏輯值 unknown 時(shí)，x=x 被判斷為 true ，而 x 是 UNKNOWN 時(shí)被判斷為 unknown 。

-- 這個(gè)是明確的邏輯值的比較 
unknown = unknown → true 
 
-- 這個(gè)相當(dāng)于NULL = NULL 
UNKNOWN = UNKNOWN → unknown 

三值邏輯的邏輯值表

NOT：

AND：

OR：

圖中藍(lán)色部分是三值邏輯中獨(dú)有的運(yùn)算，這在二值邏輯中是沒(méi)有的。其余的 SQL 謂詞全部都能由這三個(gè)邏輯運(yùn)算組合而來(lái)。從這個(gè)意義上講，這個(gè)幾個(gè)邏輯表可以說(shuō)是 SQL 的母體(matrix)。

NOT 的話(huà)，因?yàn)檫壿嬛当肀容^簡(jiǎn)單，所以很好記;但是對(duì)于 AND 和 OR，因?yàn)榻M合出來(lái)的邏輯值較多，所以全部記住非常困難。為了便于記憶，請(qǐng)注意這三個(gè)邏輯值之間有下面這樣的優(yōu)先級(jí)順序：

• AND 的情況：false > unknown > true;
• OR 的情況：true > unknown > false。

優(yōu)先級(jí)高的邏輯值會(huì)決定計(jì)算結(jié)果。例如 true AND unknown ，因?yàn)?unknown 的優(yōu)先級(jí)更高，所以結(jié)果是 unknown 。而 true OR unknown 的話(huà)，因?yàn)?true 優(yōu)先級(jí)更高，所以結(jié)果是 true 。

記住這個(gè)順序后就能更方便地進(jìn)行三值邏輯運(yùn)算了。特別需要記住的是，當(dāng) AND 運(yùn)算中包含 unknown 時(shí)，結(jié)果肯定不會(huì)是 true (反之，如果AND 運(yùn)算結(jié)果為 true ，則參與運(yùn)算的雙方必須都為 true )。

-- 假設(shè) a = 2, b = 5, c = NULL，下列表達(dá)式的邏輯值如下 
 
a < b AND b > c  → unknown 
a > b OR b < c   → unknown 
a < b OR b < c   → true 
NOT (b <> c)     → unknown 

“IS NULL” 而非 “= NULL”

我們?cè)倩氐絾?wèn)題：為什么必須寫(xiě)成“IS NULL”，而不是“= NULL”?

對(duì) NULL 使用比較謂詞后得到的結(jié)果總是 unknown 。而查詢(xún)結(jié)果只會(huì)包含 WHERE 子句里的判斷結(jié)果為 true 的行，不會(huì)包含判斷結(jié)果為 false 和 unknown 的行。不只是等號(hào)，對(duì) NULL 使用其他比較謂詞，結(jié)果也都是一樣的。

所以無(wú)論 remark 是不是 NULL ，比較結(jié)果都是 unknown ，那么永遠(yuǎn)沒(méi)有結(jié)果返回。以下的式子都會(huì)被判為 unknown：

-- 以下的式子都會(huì)被判為 unknown

-- 以下的式子都會(huì)被判為 unknown 
= NULL 
> NULL 
< NULL 
<> NULL 
NULL = NULL 

那么，為什么對(duì) NULL 使用比較謂詞后得到的結(jié)果永遠(yuǎn)不可能為真呢?

這是因?yàn)椋琋ULL 既不是值也不是變量。NULL 只是一個(gè)表示“沒(méi)有值”的標(biāo)記，而比較謂詞只適用于值。

因此，對(duì)并非值的 NULL 使用比較謂詞本來(lái)就是沒(méi)有意義的。“列的值為 “NULL ”、“NULL 值” 這樣的說(shuō)法本身就是錯(cuò)誤的。因?yàn)? NULL不是值，所以不在定義域(domain)中。

相反，如果有人認(rèn)為 NULL 是值，那么我們可以倒過(guò)來(lái)想一下：它是什么類(lèi)型的值?關(guān)系數(shù)據(jù)庫(kù)中存在的值必然屬于某種類(lèi)型，比如字符型或數(shù)值型等。所以，假如 NULL 是值，那么它就必須屬于某種類(lèi)型。

NULL 容易被認(rèn)為是值的原因有兩個(gè)。

第一個(gè)是高級(jí)編程語(yǔ)言里面，NULL 被定義為了一個(gè)常量(很多語(yǔ)言將其定義為了整數(shù)0)，這導(dǎo)致了我們的混淆。但是，SQL 里的 NULL 和其他編程語(yǔ)言里的 NULL 是完全不同的東西。

第二個(gè)原因是，IS NULL 這樣的謂詞是由兩個(gè)單詞構(gòu)成的，所以我們?nèi)菀装?IS 當(dāng)作謂詞，而把 NULL 當(dāng)作值。特別是 SQL 里還有 IS TRUE 、IS FALSE 這樣的謂詞，我們由此類(lèi)推，從而這樣認(rèn)為也不是沒(méi)有道理。但是正如講解標(biāo)準(zhǔn) SQL 的書(shū)里提醒人們注意的那樣，我們應(yīng)該把 IS NULL 看作是一個(gè)謂詞。因此，寫(xiě)成 IS_NULL 這樣也許更合適。

溫柔的陷阱

比較謂詞和 NULL

排中律不成立。排中律指同一個(gè)思維過(guò)程中，兩個(gè)相互矛盾的思想不能同假，必有一真，即“要么A要么非A”。

假設(shè)我們有學(xué)生表：t_student：

DROP TABLE IF EXISTS t_student; 
CREATE TABLE t_student ( 
    id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', 
    name VARCHAR(50) NOT NULL COMMENT '名稱(chēng)', 
    age INT(3) COMMENT '年齡', 
    remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '備注', 
    primary key(id) 
) COMMENT '學(xué)生信息'; 
 
INSERT INTO t_student(name, age) 
VALUE('zhangsan', 25),('wangwu', 60),('bruce', 32),('yzb', NULL),('boss', 18); 
 
SELECT * FROM t_student; 

表中數(shù)據(jù) yzb 的 age 是 NULL，也就是說(shuō) yzb 的年齡未知。在現(xiàn)實(shí)世界里，yzb 是 20 歲，或者不是 20 歲，二者必居其一，這毫無(wú)疑問(wèn)是一個(gè)真命題。那么在 SQL 的世界里了，排中律還適用嗎? 我們來(lái)看一個(gè) SQL ：

SELECT * FROM t_student 
WHERE age = 20 OR age <> 20; 

咋一看，這不就是查詢(xún)表中全部記錄嗎?我們來(lái)看下實(shí)際結(jié)果：

yzb 沒(méi)查出來(lái)，這是為什么?我們來(lái)分析下，yzb 的 age 是 NULL，那么這條記錄的判斷步驟如下：

-- 1. 約翰年齡是 NULL (未知的 NULL !) 
SELECT * 
FROM t_student 
WHERE age = NULL 
OR age <> NULL; 
 
-- 2. 對(duì) NULL 使用比較謂詞后，結(jié)果為unknown 
SELECT * 
FROM t_student 
WHERE unknown 
OR unknown; 
 
-- 3．unknown OR unknown 的結(jié)果是unknown (參考三值邏輯的邏輯值表) 
SELECT * 
FROM t_student 
WHERE unknown; 

SQL 語(yǔ)句的查詢(xún)結(jié)果里只有判斷結(jié)果為 true 的行。要想讓 yzb 出現(xiàn)在結(jié)果里，需要添加下面這樣的 “第 3 個(gè)條件”：

-- 添加 3 個(gè)條件：年齡是20 歲，或者不是20 歲，或者年齡未知。

-- 添加 3 個(gè)條件：年齡是20 歲，或者不是20 歲，或者年齡未知 
SELECT * FROM t_student 
WHERE age = 20  
    OR age <> 20 
    OR age IS NULL; 

CASE 表達(dá)式和 NULL。簡(jiǎn)單 CASE 表達(dá)式如下：

CASE col_1 
    WHEN = 1 THEN 'o' 
    WHEN NULL THEN 'x' 
END 

這個(gè) CASE 表達(dá)式一定不會(huì)返回 ×。這是因?yàn)?，第二個(gè) WHEN 子句是 col_1 = NULL 的縮寫(xiě)形式。正如我們所知，這個(gè)式子的邏輯值永遠(yuǎn)是 unknown ，而且 CASE 表達(dá)式的判斷方法與 WHERE 子句一樣，只認(rèn)可邏輯值為 true 的條件。正確的寫(xiě)法是像下面這樣使用搜索 CASE 表達(dá)式：

CASE WHEN col_1 = 1 THEN 'o' 
    WHEN col_1 IS NULL THEN 'x' 
END 

NOT IN 和 NOT EXISTS 并非等價(jià)

我們?cè)趯?duì) SQL 語(yǔ)句進(jìn)行性能優(yōu)化時(shí)，經(jīng)常用到的一個(gè)技巧是將 IN 改寫(xiě)成 EXISTS ，這是等價(jià)改寫(xiě)，并沒(méi)有什么問(wèn)題。但是，將 NOT IN 改寫(xiě)成 NOT EXISTS 時(shí)，結(jié)果未必一樣。

我們來(lái)看個(gè)例子，我們有如下兩張表：t_student_A 和 t_student_B，分別表示 A 班學(xué)生與 B 班學(xué)生。

DROP TABLE IF EXISTS t_student_A; 
CREATE TABLE t_student_A ( 
    id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', 
    name VARCHAR(50) NOT NULL COMMENT '名稱(chēng)', 
    age INT(3) COMMENT '年齡', 
    city VARCHAR(50) NOT NULL COMMENT '城市', 
    remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '備注', 
    primary key(id) 
) COMMENT '學(xué)生信息'; 
 
INSERT INTO t_student_A(name, age, city) 
VALUE 
('zhangsan', 25,'深圳市'),('wangwu', 60, '廣州市'), 
('bruce', 32, '北京市'),('yzb', NULL, '深圳市'), 
('boss', 43, '深圳市'); 
 
DROP TABLE IF EXISTS t_student_B; 
CREATE TABLE t_student_B ( 
    id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', 
    name VARCHAR(50) NOT NULL COMMENT '名稱(chēng)', 
    age INT(3) COMMENT '年齡', 
    city VARCHAR(50) NOT NULL COMMENT '城市', 
    remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '備注', 
    primary key(id) 
) COMMENT '學(xué)生信息'; 
 
INSERT INTO t_student_B(name, age, city) 
VALUE 
('馬化騰', 45, '深圳市'),('馬三', 25, '深圳市'), 
('馬云', 43, '杭州市'),('李彥宏', 41, '深圳市'), 
('年輕人', 25, '深圳市'); 
 
SELECT * FROM t_student_A; 
SELECT * FROM t_student_B; 

需求：查詢(xún)與 A 班住在深圳的學(xué)生年齡不同的 B 班學(xué)生，也就說(shuō)查詢(xún)出 ：馬化騰 和 李彥宏，這個(gè) SQL 該如何寫(xiě)，像這樣?

-- 查詢(xún)與 A  班住在深圳的學(xué)生年齡不同的 B 班學(xué)生 ? 
SELECT * FROM t_student_B 
WHERE age NOT IN ( 
    SELECT age FROM t_student_A  
    WHERE city = '深圳市' 
); 

我們來(lái)看下執(zhí)行結(jié)果：

我們發(fā)現(xiàn)結(jié)果是空，查詢(xún)不到任何數(shù)據(jù)，這是為什么 ?這里 NULL 又開(kāi)始作怪了，我們一步一步來(lái)看看究竟發(fā)生了什么。

-- 1. 執(zhí)行子查詢(xún)，獲取年齡列表 
SELECT * FROM t_student 
WHERE age NOT IN(43, NULL, 25); 
 
-- 2. 用NOT 和IN 等價(jià)改寫(xiě)NOT IN 
SELECT * FROM t_student 
WHERE NOT age IN (43, NULL, 25); 
 
-- 3. 用OR 等價(jià)改寫(xiě)謂詞IN 
SELECT * FROM t_student 
WHERE NOT ( (age = 43) OR (age = NULL) OR (age = 25) ); 
 
-- 4. 使用德· 摩根定律等價(jià)改寫(xiě) 
SELECT * FROM t_student 
WHERE NOT (age = 43) AND NOT(age = NULL) AND NOT (age = 25); 
 
-- 5. 用<> 等價(jià)改寫(xiě) NOT 和 = 
SELECT * FROM t_student 
WHERE (age <> 43) AND (age <> NULL) AND (age <> 25); 
 
-- 6. 對(duì)NULL 使用<> 后，結(jié)果為 unknown 
SELECT * FROM t_student 
WHERE (age <> 43) AND unknown AND (age <> 25); 
 
-- 7．如果 AND 運(yùn)算里包含 unknown，則結(jié)果不為true（參考三值邏輯的邏輯值表） 
SELECT * FROM t_student 
WHERE false 或 unknown; 

可以看出，在進(jìn)行了一系列的轉(zhuǎn)換后，沒(méi)有一條記錄在 WHERE 子句里被判斷為 true 。也就是說(shuō)，如果 NOT IN 子查詢(xún)中用到的表里被選擇的列中存在 NULL ，則 SQL 語(yǔ)句整體的查詢(xún)結(jié)果永遠(yuǎn)是空。這是很可怕的現(xiàn)象!

為了得到正確的結(jié)果，我們需要使用 EXISTS 謂詞。

-- 正確的SQL 語(yǔ)句：馬化騰和李彥宏將被查詢(xún)到 
SELECT * FROM t_student_B B 
WHERE NOT EXISTS (  
    SELECT * FROM t_student_A A 
    WHERE B.age = A.age 
    AND A.city = '深圳市'  
); 

執(zhí)行結(jié)果如下：

同樣地，我們?cè)賮?lái)一步一步地看看這段 SQL 是如何處理年齡為 NULL 的行的：

-- 1. 在子查詢(xún)里和 NULL 進(jìn)行比較運(yùn)算，此時(shí) A.age 是 NULL 
SELECT * FROM t_student_B B 
WHERE NOT EXISTS (  
    SELECT * FROM t_student_A A 
    WHERE B.age = NULL 
    AND A.city = '深圳市'  
); 
 
-- 2. 對(duì)NULL 使用“=”后，結(jié)果為 unknown 
SELECT * FROM t_student_B B 
WHERE NOT EXISTS (  
    SELECT * FROM t_student_A A 
    WHERE unknown 
    AND A.city = '深圳市'  
); 
 
-- 3. 如果AND 運(yùn)算里包含 unknown，結(jié)果不會(huì)是true 
SELECT * FROM t_student_B B 
WHERE NOT EXISTS (  
    SELECT * FROM t_student_A A 
    WHERE false 或 unknown 
); 
 
-- 4. 子查詢(xún)沒(méi)有返回結(jié)果，因此相反地，NOT EXISTS 為 true 
SELECT * FROM t_student_B B 
WHERE true; 

也就是說(shuō)，yzb 被作為 “與任何人的年齡都不同的人” 來(lái)處理了。EXISTS 只會(huì)返回 true 或者false，永遠(yuǎn)不會(huì)返回 unknown。因此就有了 IN 和 EXISTS 可以互相替換使用，而 NOT IN和 NOT EXISTS 卻不可以互相替換的混亂現(xiàn)象。

還有一些其他的陷阱，比如：限定謂詞和 NULL、限定謂詞和極值函數(shù)不是等價(jià)的、聚合函數(shù)和 NULL 等等。

總結(jié)

1、NULL 用于表示缺失的值或遺漏的未知數(shù)據(jù)，不是某種具體類(lèi)型的值，不能對(duì)其使用謂詞。

2、對(duì) NULL 使用謂詞后的結(jié)果是 unknown，unknown 參與到邏輯運(yùn)算時(shí)，SQL 的運(yùn)行會(huì)和預(yù)想的不一樣。

3、 IS NULL 整個(gè)是一個(gè)謂詞，而不是：IS 是謂詞，NULL 是值;類(lèi)似的還有 IS TRUE、IS FALSE。

4、要想解決 NULL 帶來(lái)的各種問(wèn)題，最佳方法應(yīng)該是往表里添加 NOT NULL 約束來(lái)盡力排除 NULL。

我的項(xiàng)目中有個(gè)硬性規(guī)定：所有字段必須是 NOT NULL，建表的時(shí)候就加上此約束。