了解三值邏輯與NULL，你離SQL高手更近了一步

什麼是NULL

NULL 用于表示缺失的值或遺漏的未知資料，不是某種具體類型的值。資料表中的 NULL 值表示該值所處的字段為空，值為 NULL 的字段沒有值，尤其要明白的是：NULL 值與 0 或者空字元串是不同的。

兩種 NULL

這種說法大家可能會覺得很奇怪，因為 SQL 裡隻存在一種 NULL 。然而在讨論 NULL 時，我們一般都會将它分成兩種類型來思考：“未知”（unknown）和“不适用”（not applicable,inapp licable）。

以“不知道戴墨鏡的人眼睛是什麼顔色”這種情況為例，這個人的眼睛肯定是有顔色的，但是如果他不摘掉眼鏡，别人就不知道他的眼睛是什麼顔色。這就叫作未知。而“不知道冰箱的眼睛是什麼顔色”則屬于“不适用”。因為冰箱根本就沒有眼睛，是以“眼睛的顔色”這一屬性并不适用于冰箱。“冰箱的眼睛的顔色”這種說法和“圓的體積”“男性的分娩次數”一樣，都是沒有意義的。平時，我們習慣了說“不知道”，但是“不知道”也分很多種。“不适用”這種情況下的 NULL ，在語義上更接近于“無意義”，而不是“不确定”。這裡總結一下：“未知”指的是“雖然現在不知道，但加上某些條件後就可以知道”；而“不适用”指的是“無論怎麼努力都無法知道”。

關系模型的發明者 E.F. Codd 最先給出了這種分類。下圖是他對“丢失的資訊”的分類

為什麼必須寫成“IS NULL”，而不是“＝ NULL”

我相信不少人有這樣的困惑吧，尤其是相信剛學 SQL 的小夥伴。我們來看個具體的案例，假設我們有如下表以及資料

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 '名稱',
    remark VARCHAR(500) COMMENT '備注',
    primary key(id)
) COMMENT 'NULL樣例';

INSERT INTO t_sample_null(name, remark)
VALUES('zhangsan', '張三'),('李四', NULL);

我們要查詢備注為 NULL 的記錄（為 NULL 這種叫法本身是不對的，隻是我們日常中已經叫習慣了，具體往下看），怎麼查，很多新手會寫出這樣的 SQL

-- SQL 不報錯，但查不出結果
SELECT * FROM t_sample_null WHERE remark = NULL;

執行時不報錯，但是查不出我們想要的結果，這是為什麼了？這個問題我們先放着，我們往下看

三值邏輯

這個三值邏輯不是三目運算，指的是三個邏輯值，有人可能有疑問了，邏輯值不是隻有真(true)和假(false)嗎，哪來的第三個？說這話時我們需要注意所處的環境，在主流的程式設計語言中（C、JAVA、Python、JS等）中，邏輯值确實隻有 2 個，但在 SQL 中卻存在第三個邏輯值：unknown。這有點類似于我們平時所說的：對、錯、不知道。

邏輯值 unknown 和作為 NULL 的一種的 UNKNOWN （未知）是不同的東西。前者是明确的布爾型的邏輯值，後者既不是值也不是變量。為了便于區分，前者采用小寫字母 unknown ，後者用大寫字母 UNKNOWN 來表示。為了讓大家了解兩者的不同，我們來看一個 x=x 這樣的簡單等式。x 是邏輯值 unknown 時，x=x 被判斷為 true ，而 x 是 UNKNOWN 時被判斷為 unknown

-- 這個是明确的邏輯值的比較
unknown = unknown → true

-- 這個相當于NULL = NULL
UNKNOWN = UNKNOWN → unknown

三值邏輯的邏輯值表

NOT

AND

圖中藍色部分是三值邏輯中獨有的運算，這在二值邏輯中是沒有的。其餘的 SQL 謂詞全部都能由這三個邏輯運算組合而來。從這個意義上講，這個幾個邏輯表可以說是 SQL 的母體（matrix）。

NOT 的話，因為邏輯值表比較簡單，是以很好記；但是對于 AND 和 OR，因為組合出來的邏輯值較多，是以全部記住非常困難。為了便于記憶，請注意這三個邏輯值之間有下面這樣的優先級順序。

AND 的情況：false ＞ unknown ＞ true

OR 的情況：true ＞ unknown ＞ false

優先級高的邏輯值會決定計算結果。例如 true AND unknown ，因為 unknown 的優先級更高，是以結果是 unknown 。而 true OR unknown 的話，因為 true 優先級更高，是以結果是 true 。記住這個順序後就能更友善地進行三值邏輯運算了。特别需要記住的是，當 AND 運算中包含 unknown 時，結果肯定不會是 true （反之，如果AND 運算結果為 true ，則參與運算的雙方必須都為 true ）。

-- 假設 a = 2, b = 5, c = NULL，下清單達式的邏輯值如下

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”

我們再回到問題：為什麼必須寫成“IS NULL”，而不是“＝ NULL”

對 NULL 使用比較謂詞後得到的結果總是 unknown 。而查詢結果隻會包含 WHERE 子句裡的判斷結果為 true 的行，不會包含判斷結果為 false 和 unknown 的行。不隻是等号，對 NULL 使用其他比較謂詞，結果也都是一樣的。是以無論 remark 是不是 NULL ，比較結果都是 unknown ，那麼永遠沒有結果傳回。以下的式子都會被判為 unknown

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

那麼，為什麼對 NULL 使用比較謂詞後得到的結果永遠不可能為真呢？這是因為，NULL 既不是值也不是變量。NULL 隻是一個表示“沒有值”的标記，而比較謂詞隻适用于值。是以，對并非值的 NULL 使用比較謂詞本來就是沒有意義的。“列的值為 NULL ”、“NULL 值” 這樣的說法本身就是錯誤的。因為 NULL不是值，是以不在定義域（domain）中。相反，如果有人認為 NULL 是值，那麼我們可以倒過來想一下：它是什麼類型的值？關系資料庫中存在的值必然屬于某種類型，比如字元型或數值型等。是以，假如 NULL 是值，那麼它就必須屬于某種類型。

NULL 容易被認為是值的原因有兩個。第一個是進階程式設計語言裡面，NULL 被定義為了一個常量（很多語言将其定義為了整數0），這導緻了我們的混淆。但是，SQL 裡的 NULL 和其他程式設計語言裡的 NULL 是完全不同的東西。第二個原因是，IS NULL 這樣的謂詞是由兩個單詞構成的，是以我們容易把 IS 當作謂詞，而把 NULL 當作值。特别是 SQL 裡還有 IS TRUE 、IS FALSE 這樣的謂詞，我們由此類推，進而這樣認為也不是沒有道理。但是正如講解标準 SQL 的書裡提醒人們注意的那樣，我們應該把 IS NULL 看作是一個謂詞。是以，寫成 IS_NULL 這樣也許更合适。

溫柔的陷阱

比較謂詞和 NULL

排中律不成立

排中律指同一個思維過程中，兩個互相沖突的思想不能同假，必有一真，即“要麼A要麼非A”
假設我們有學生表：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 '名稱',
    age INT(3) COMMENT '年齡',
    remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '備注',
    primary key(id)
) COMMENT '學生資訊';

INSERT INTO t_student(name, age)
VALUE('zhangsan', 25),('wangwu', 60),('bruce', 32),('yzb', NULL),('boss', 18);

SELECT * FROM t_student;

表中資料 yzb 的 age 是 NULL，也就是說 yzb 的年齡未知。在現實世界裡，yzb 是 20 歲，或者不是 20 歲，二者必居其一，這毫無疑問是一個真命題。那麼在 SQL 的世界裡了，排中律還适用嗎? 我們來看一個 SQL

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

咋一看，這不就是查詢表中全部記錄嗎？我們來看下實際結果

yzb 沒查出來，這是為什麼了？我們來分析下，yzb 的 age 是 NULL，那麼這條記錄的判斷步驟如下

-- 1. 約翰年齡是 NULL (未知的 NULL !)
SELECT *
FROM t_student
WHERE age = NULL
OR age <> NULL;

-- 2. 對 NULL 使用比較謂詞後，結果為unknown
SELECT *
FROM t_student
WHERE unknown
OR unknown;

-- 3．unknown OR unknown 的結果是unknown (參考三值邏輯的邏輯值表)
SELECT *
FROM t_student
WHERE unknown;

SQL 語句的查詢結果裡隻有判斷結果為 true 的行。要想讓 yzb 出現在結果裡，需要添加下面這樣的 “第 3 個條件”

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

CASE 表達式和 NULL

簡單 CASE 表達式如下

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

這個 CASE 表達式一定不會傳回 ×。這是因為，第二個 WHEN 子句是 col_1 = NULL 的縮寫形式。正如我們所知，這個式子的邏輯值永遠是 unknown ，而且 CASE 表達式的判斷方法與 WHERE 子句一樣，隻認可邏輯值為 true 的條件。正确的寫法是像下面這樣使用搜尋 CASE 表達式

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

NOT IN 和 NOT EXISTS 不是等價的

我們在對 SQL 語句進行性能優化時，經常用到的一個技巧是将 IN 改寫成 EXISTS ，這是等價改寫，并沒有什麼問題。但是，将 NOT IN 改寫成 NOT EXISTS 時，結果未必一樣。

我們來看個例子，我們有如下兩張表：t_student_A 和 t_student_B，分别表示 A 班學生與 B 班學生

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 '名稱',
    age INT(3) COMMENT '年齡',
    city VARCHAR(50) NOT NULL COMMENT '城市',
    remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '備注',
    primary key(id)
) COMMENT '學生資訊';

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 '名稱',
    age INT(3) COMMENT '年齡',
    city VARCHAR(50) NOT NULL COMMENT '城市',
    remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '備注',
    primary key(id)
) COMMENT '學生資訊';

INSERT INTO t_student_B(name, age, city)
VALUE
('馬化騰', 45, '深圳市'),('馬三', 25, '深圳市'),
('馬雲', 43, '杭州市'),('李彥宏', 41, '深圳市'),
('年輕人', 25, '深圳市');

 * FROM t_student_B;

需求：查詢與 A 班住在深圳的學生年齡不同的 B 班學生，也就說查詢出：馬化騰和李彥宏，這個 SQL 該如何寫，像這樣？

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

我們來看下執行結果

我們發現結果是空，查詢不到任何資料，這是為什麼了？這裡 NULL 又開始作怪了，我們一步一步來看看究竟發生了什麼

-- 1. 執行子查詢，擷取年齡清單
SELECT * FROM t_student
WHERE age NOT IN(43, NULL, 25);

-- 2. 用NOT 和IN 等價改寫NOT IN
SELECT * FROM t_student
WHERE NOT age IN (43, NULL, 25);

-- 3. 用OR 等價改寫謂詞IN
SELECT * FROM t_student
WHERE NOT ( (age = 43) OR (age = NULL) OR (age = 25) );

-- 4. 使用德· 摩根定律等價改寫
SELECT * FROM t_student
WHERE NOT (age = 43) AND NOT(age = NULL) AND NOT (age = 25);

-- 5. 用<> 等價改寫 NOT 和 =
SELECT * FROM t_student
WHERE (age <> 43) AND (age <> NULL) AND (age <> 25);

-- 6. 對NULL 使用<> 後，結果為 unknown
SELECT * FROM t_student
WHERE (age <> 43) AND unknown AND (age <> 25);

-- 7．如果 AND 運算裡包含 unknown，則結果不為true（參考三值邏輯的邏輯值表）
SELECT * FROM t_student
WHERE false 或 unknown;

可以看出，在進行了一系列的轉換後，沒有一條記錄在 WHERE 子句裡被判斷為 true 。也就是說，如果 NOT IN 子查詢中用到的表裡被選擇的列中存在 NULL ，則 SQL 語句整體的查詢結果永遠是空。這是很可怕的現象！

為了得到正确的結果，我們需要使用 EXISTS 謂詞

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

執行結果如下

同樣地，我們再來一步一步地看看這段 SQL 是如何處理年齡為 NULL 的行的

-- 1. 在子查詢裡和 NULL 進行比較運算，此時 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. 對NULL 使用“=”後，結果為 unknown
SELECT * FROM t_student_B B
WHERE NOT EXISTS (
    SELECT * FROM t_student_A A
    WHERE unknown
    AND A.city = '深圳市' 
);

-- 3. 如果AND 運算裡包含 unknown，結果不會是true
SELECT * FROM t_student_B B
WHERE NOT EXISTS (
    SELECT * FROM t_student_A A
    WHERE false 或 unknown
);

-- 4. 子查詢沒有傳回結果，是以相反地，NOT EXISTS 為 true
SELECT * FROM t_student_B B
WHERE true;

也就是說，yzb 被作為 “與任何人的年齡都不同的人” 來處理了。EXISTS 隻會傳回 true 或者false，永遠不會傳回 unknown。是以就有了 IN 和 EXISTS 可以互相替換使用，而 NOT IN和 NOT EXISTS 卻不可以互相替換的混亂現象。

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

總結

1、NULL 用于表示缺失的值或遺漏的未知資料，不是某種具體類型的值，不能對其使用謂

2、對 NULL 使用謂詞後的結果是 unknown，unknown 參與到邏輯運算時，SQL 的運作會和預想的不一樣3、 IS NULL 整個是一個謂詞，而不是：IS 是謂詞，NULL 是值；類似的還有 IS TRUE、IS FALSE4、要想解決 NULL 帶來的各種問題，最佳方法應該是往表裡添加 NOT NULL 限制來盡力排除 NULL

本文分享自華為雲3MS社群《SQL的溫柔陷阱：三值邏輯與NULL》，原文作者：範靓。

點選關注，第一時間了解華為雲新鮮技術~

了解三值邏輯與NULL，你離SQL高手更近了一步

什麼是NULL

兩種 NULL

為什麼必須寫成“IS NULL”，而不是“＝ NULL”

三值邏輯

三值邏輯的邏輯值表

“IS NULL” 而非 “= NULL”

溫柔的陷阱

比較謂詞和 NULL

排中律不成立

NOT IN 和 NOT EXISTS 不是等價的

總結

繼續閱讀

線上教育巨頭多鄰國Duolingo入華一周年，中國市場馬力全開

我做“内網知識星球”一周年總結

Oracle的基本操作

程式設計高手：VC 6.0下利用消息實作内部程序通訊

如何在 Linux 上安裝應用程式？

Java基礎階段總結——練習小項目+IDEA使用說明+Java方法+關鍵字return+debug總結練習

轉換字元串成ＩＮＴ６４

c寫檔案

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

JVM記憶體管理、直接記憶體和垃圾回收

Android – ListView 中添加按鈕，動态删除添加ItemView的操作

neo4j之cypher使用文檔

sqlServer根據經緯查距離