SQL 是一種聲明式的語(yǔ)言，我們只需要描述想要的結(jié)果（WHAT），而不關(guān)心數(shù)據(jù)庫(kù)如何實(shí)現(xiàn)（HOW）；雖然 SQL 比較容易學(xué)習(xí)，但是仍然有一些容易混淆和出錯(cuò)的概念。

今天我們就來(lái)說(shuō)說(shuō) SQL 中的空值陷阱和避坑方法，涉及的數(shù)據(jù)庫(kù)包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。還是老規(guī)矩，結(jié)論先行：

NULL 即是空

在數(shù)據(jù)庫(kù)中，空值（NULL）是一個(gè)特殊的值，通常用于表示缺失值或者不適用的值。比如，填寫(xiě)問(wèn)卷時(shí)不愿意透露某些信息會(huì)導(dǎo)致錄入項(xiàng)的缺失，在公司的組織結(jié)構(gòu)中總會(huì)有一個(gè)人（董事長(zhǎng)/總經(jīng)理）沒(méi)有上級(jí)領(lǐng)導(dǎo)。

首先一點(diǎn)，空值與數(shù)字 0 并不相同。假如我問(wèn)你：你的錢(qián)包里有多少錢(qián)？如果你知道里面沒(méi)有錢(qián)，可以說(shuō)是零；如果你不確定，那么就是未知，但不能說(shuō)沒(méi)有。當(dāng)我們需要?jiǎng)?chuàng)建一個(gè)表來(lái)存儲(chǔ)這個(gè)信息的時(shí)候，應(yīng)該是 NULL；除非我們能夠確定錢(qián)包里面沒(méi)有錢(qián)或者有多少錢(qián)。

另外，空值與空字符串（''）也不相同，原因和上面類(lèi)似。但是 Oracle 是一個(gè)例外，我們會(huì)在下文具體討論。

在大多數(shù)編程語(yǔ)言中，訪(fǎng)問(wèn) null 值通常會(huì)導(dǎo)致錯(cuò)誤；但是 SQL 不會(huì)出錯(cuò)，只是會(huì)影響到運(yùn)算的結(jié)果而已。

三值邏輯

在大多數(shù)編程語(yǔ)言中，邏輯運(yùn)算的結(jié)果只有兩種情況，不是真（True）就是假（False）。但是對(duì)于 SQL 而言，邏輯運(yùn)算還可能是未知（Unknown）：

引入三值邏輯主要是為了支持 NULL，因?yàn)?NULL 代表的是未知數(shù)據(jù)。因此，SQL 中的邏輯運(yùn)算與（AND）、或（OR）以及非（NOT）的結(jié)果如下：

對(duì)于 AND 運(yùn)算符而言，真和未知的與運(yùn)算有可能是真，也有可能是假；因此，最終的結(jié)果是未知。

> 📝SQL 中的 WHERE、HAVING 以及 CASE WHEN 子句只返回邏輯運(yùn)算結(jié)果為真的數(shù)據(jù)，不返回結(jié)果為假或未知的數(shù)據(jù)。

空值比較

當(dāng)我們使用比較運(yùn)算符（=、<>、<、> 等）與 NULL 進(jìn)行比較時(shí)，結(jié)果既不是真也不是假，而是未知；因?yàn)?NULL 表示未知，也就意味著可能是任何值。以下運(yùn)算的結(jié)果都是未知：

NULL = 0NULL <> 0NULL <= 0NULL = NULLNULL != NULL

NULL 與任何值都不相等，甚至兩個(gè) NULL 也不相等；因?yàn)槲覀儾荒苷f(shuō)兩個(gè)未知的值相同，也不能說(shuō)它們不相同。

> ⚠️對(duì)于比較運(yùn)算而言，NULL 和 NULL 不相同；但是某些 SQL 子句中的 NULL 值被看作相同的值，例如 GROUP BY。具體參考下文。

那么，如何判斷一個(gè)值是否是 NULL 呢？為此，SQL 引入了兩個(gè)謂詞（WHERE 子句）：IS NULL和IS NOT NULL。以下示例用于查找 manager 為空的員工：

-- 使用比較運(yùn)算符判斷空值SELECT employee_id, first_name, last_name, manager_id  FROM employees WHERE manager_id = NULL;employee_id|first_name|last_name|manager_id|-----------|----------|---------|----------|
-- 使用 IS NULL 判斷空值SELECT employee_id, first_name, last_name, manager_id  FROM employees WHERE manager_id IS NULL;employee_id|first_name|last_name|manager_id|-----------|----------|---------|----------|        100|Steven    |King     |          |

其中，第一個(gè)查詢(xún)使用比較運(yùn)算符判斷空值，不會(huì)返回任何結(jié)果；第二個(gè)查詢(xún)使用 IS NULL 判斷空值，返回了正確的結(jié)果。

除了標(biāo)準(zhǔn)的 IS [NOT] NULL 之外，還有一些數(shù)據(jù)庫(kù)擴(kuò)展的運(yùn)算符可以用于空值比較：

-- MySQLSELECT employee_id, first_name, last_name, manager_id  FROM employees WHERE manager_id <=> NULL;employee_id|first_name|last_name|manager_id|-----------|----------|---------|----------|        100|Steven    |King     |          |

-- PostgreSQLSELECT employee_id, first_name, last_name, manager_id  FROM employees WHERE manager_id IS NOT DISTINCT FROM NULL;employee_id|first_name|last_name|manager_id|-----------|----------|---------|----------|        100|Steven    |King     |          |

MySQL 中的<=>可以用于等值比較，支持兩個(gè) NULL 值；PostgreSQL 中的IS [NOT] DISTINCT FROM可以用于等值比較，支持兩個(gè) NULL 值。

以下查詢(xún)的結(jié)果也不會(huì)返回任何結(jié)果：

SELECT employee_id, first_name, last_name, manager_id  FROM employees WHERE (1 = NULL) OR (1 != NULL);

因?yàn)楦鶕?jù)上面的三值邏輯，兩個(gè)未知結(jié)果的 OR 運(yùn)算最終還是未知。

前文我們說(shuō)過(guò)，空字符串不是 NULL；但是 Oracle 中的空字符串被看作 NULL。例如：

-- OracleSELECT 1  FROM dual WHERE '' IS NULL;VAL|---|  1|
-- 其他數(shù)據(jù)庫(kù)SELECT 1 AS val WHERE '' IS NULL;val|---|

當(dāng)然，我們?nèi)绻褂玫戎担?）運(yùn)算符判斷空字符串與 NULL，結(jié)果仍然為空。

NOT IN 與空值

對(duì)于 WHERE 條件中的 IN 和 NOT IN 運(yùn)算符，使用的是等值比較。所以如果 NOT IN 碰到了 NULL 值，永遠(yuǎn)不會(huì)返回任何結(jié)果。例如：

SELECT employee_id, first_name, last_name, manager_id  FROM employees WHERE 1 NOT IN (NULL, 2);

因?yàn)樯厦娴臈l件實(shí)際上等價(jià)于：

SELECT employee_id, first_name, last_name, manager_id  FROM employees WHERE 1 != NULL AND 1 != 2;

1 不等于 NULL 的結(jié)果是未知，1 不等于 2 的結(jié)果是真，未知和真的 AND 運(yùn)算結(jié)果還是未知。

> ⚠️如果使用 NOT IN，一定要確保括號(hào)中的值不會(huì)出現(xiàn)  NULL；或者盡量使用 NOT EXISTS。

函數(shù)與空值

一般來(lái)說(shuō)，函數(shù)和表達(dá)式的參數(shù)中如果存在 NULL，其結(jié)果也是 NULL。當(dāng)然也有一些例外，比如聚合函數(shù)。

以下查詢(xún)返回的都是 NULL：

SELECT ABS(NULL), 1 + NULL  FROM employees WHERE employee_id = 100;ABS(NULL)|1 + NULL|---------|--------|   [NULL]|  [NULL]|

一個(gè)未知值的絕對(duì)值仍然未知，1 加上一個(gè)未知值結(jié)果還是未知。

但是一個(gè)常見(jiàn)的例外是字符串與 NULL 的連接：

-- Oracle、SQL Server、PostgreSQLSELECT CONCAT('Hello', NULL)  FROM employees WHERE employee_id = 100;CONCAT('HELLO',NULL)|--------------------|Hello               |-- MySQLSELECT CONCAT('Hello', NULL)  FROM employees WHERE employee_id = 100;CONCAT('Hello', NULL)|---------------------|               [NULL]|

Oracle 將 NULL 看作空字符串，所以查詢(xún)結(jié)果為“Hello”；SQL Server 和 PostgreSQL 雖然區(qū)分了 NULL 和空字符串，但是 CONCAT 函數(shù)中這兩者等價(jià)；MySQL 中 NULL 參數(shù)導(dǎo)致 CONCAT 函數(shù)結(jié)果為 NULL；SQLite 沒(méi)有提供 CONCAT 函數(shù)。

另外，Oracle 中的 || 也將 NULL 看作空字符串；其他數(shù)據(jù)庫(kù) || 中的 NULL 將參數(shù)會(huì)產(chǎn)生 NULL 結(jié)果；SQL Server 中使用 + 連接字符串，NULL 參數(shù)將會(huì)產(chǎn)生 NULL 結(jié)果。

聚合函數(shù)（SUM、COUNT、AVG 等）通常會(huì)在進(jìn)行計(jì)算之前刪除 NULL 數(shù)據(jù)：

SELECT SUM(salary + commission_pct) sum1,       SUM(salary) + SUM(commission_pct) sum2,       COUNT(salary),       COUNT(commission_pct)  FROM employees;SUM1    |SUM2    |COUNT(SALARY)|COUNT(COMMISSION_PCT)|--------|--------|-------------|---------------------|311507.8|691423.8|          107|                   35|

第一個(gè) SUM 函數(shù)返回的是 salary 和 commission_pct 都不為空的數(shù)據(jù)總和；第二個(gè) SUM 函數(shù)返回的是 salary 不為空的數(shù)據(jù)總和加上 commission_pct 不為空的數(shù)據(jù)總和，所以比第一個(gè)數(shù)據(jù)大；COUNT 函數(shù)結(jié)果顯示 salary 有 107 條記錄不為空，commission_pct 只有 35 條記錄不為空。

如果輸入數(shù)據(jù)都是 NULL 值，除了 COUNT 函數(shù)之外的其他聚合函數(shù)返回 NULL：

SELECT COUNT(*), COUNT(commission_pct), AVG(commission_pct), SUM(commission_pct)  FROM employees WHERE commission_pct IS NULL;COUNT(*)|COUNT(COMMISSION_PCT)|AVG(COMMISSION_PCT)|SUM(COMMISSION_PCT)|--------|---------------------|-------------------|-------------------|      72|                    0|             [NULL]|             [NULL]|

COUNT(*) 函數(shù)總是返回所有數(shù)據(jù)的行數(shù)，不受空值的影響；COUNT(commission_pct) 返回了 0；AVG 和 SUM 函數(shù)返回了 NULL。

DISTINCT、GROUP BY、UNION 與空值

SQL 中的分組操作將所有的 NULL 值分到同一個(gè)組，包括 DISTINCT、GROUP BY 以及窗口函數(shù)中的 PARTITION BY。例如：

SELECT DISTINCT commission_pct  FROM employees;commission_pct|--------------|        [NULL]|          0.40|          0.30|          0.20|          0.25|          0.15|          0.35|          0.10|
SELECT commission_pct  FROM employees GROUP BY commission_pct;commission_pct|--------------|        [NULL]|          0.40|          0.30|          0.20|          0.25|          0.15|          0.35|          0.10|

從上面的示例可以看出，commission_pct 為空的數(shù)據(jù)有 72 條，但是分組之后只有一個(gè) NULL 組。

除此之外，UNION 操作符也將所有的 NULL 看作相同值：

SELECT manager_id  FROM employees WHERE manager_id IS NULL UNIONSELECT manager_id  FROM employees WHERE manager_id IS NULL;manager_id|----------|    [NULL]|

如果將 UNION 換成 UNION ALL，查詢(xún)結(jié)果將會(huì)保留 2 個(gè) NULL 值。

ORDER BY 與空值

SQL 標(biāo)準(zhǔn)沒(méi)有定義 NULL 值的排序順序，但是為 ORDER BY 定義了 NULLS FIRST 和 NULLS LAST 選項(xiàng)，用于明確指定空值排在其他數(shù)據(jù)之前或者之后。

不同數(shù)據(jù)庫(kù)對(duì)此提供了不同的實(shí)現(xiàn)：

SELECT employee_id, manager_id  FROM employees WHERE employee_id IN (100, 101, 102) ORDER BY manager_id;-- Oracle、PostgreSQLEMPLOYEE_ID|MANAGER_ID|-----------|----------|        101|       100|        102|       100|        100|    [NULL]|-- MySQL、SQL Server、SQLiteemployee_id|manager_id|-----------|----------|        100|    [NULL]|        101|       100|        102|       100|

其中，Oracle 和 PostgreSQL 默認(rèn)將 NULL 作為最大值，升序時(shí)排在最后；MySQL、SQL Server 和 SQLite 默認(rèn)將 NULL 作為最小值，升序時(shí)排在最前。

另外，Oracle、PostgreSQL 和 SQLite 提供了擴(kuò)展的 NULLS FIRST 和 NULLS LAST 選項(xiàng)：

-- Oracle、PostgreSQL 和 SQLiteSELECT employee_id, manager_id  FROM employees WHERE employee_id IN (100, 101, 102) ORDER BY manager_id NULLS FIRST;employee_id|manager_id|-----------|----------|        100|    [NULL]|        101|       100|        102|       100|

我們也可以使用 CASE 表達(dá)式實(shí)現(xiàn)類(lèi)似的效果。以下示例與 NULLS LAST 作用相同，而且所有數(shù)據(jù)庫(kù)都可以使用：

SELECT employee_id, manager_id  FROM employees WHERE employee_id IN (100, 101, 102) ORDER BY CASE WHEN manager_id IS NULL THEN 1               ELSE 0          END,          manager_id;employee_id|manager_id|-----------|----------|        101|       100|        102|       100|        100|    [NULL]|

首先，CASE 表達(dá)式將 manager_id 為空的數(shù)據(jù)轉(zhuǎn)換為 1，非空的數(shù)據(jù)轉(zhuǎn)換為 0，所以空值排在其他數(shù)據(jù)之后；第二個(gè)排序字段 manager_id 確保了非空的數(shù)據(jù)從小到大排序。

空值處理函數(shù)

由于空值的特殊性，我們?cè)诜治鰯?shù)據(jù)時(shí)經(jīng)常需要進(jìn)行空值和其他值的轉(zhuǎn)換。為此，SQL 提供了兩個(gè)標(biāo)準(zhǔn)的空值函數(shù)：COALESCE 和 NULLIF。

COALESCE(exp1, exp2, ...) 函數(shù)用于將 NULL 轉(zhuǎn)換為其他值。當(dāng) exp1 不為空時(shí)返回 exp1，否則檢查 exp2；如果 exp2 不為空時(shí)返回 exp2，以此類(lèi)推。例如：

SELECT COALESCE(NULL, NULL, 3)  FROM employees WHERE employee_id = 100;COALESCE(NULL, NULL, 3)|-----------------------|                      3|

由于前面兩個(gè)參數(shù)都是 NULL，COALESCE 最終返回了 3。

COALESCE 函數(shù)也可以使用 CASE 表達(dá)式改寫(xiě)如下：

CASE WHEN exp1 IS NOT NULL THEN exp1     WHEN exp2 IS NOT NULL THEN exp2     ...     ELSE expNEND

NULLIF(exp1, exp2) 函數(shù)用于將指定值轉(zhuǎn)換為 NULL。當(dāng) exp1 等于 exp2 時(shí)，返回 NULL；否則，返回 exp1 。NULLIF 最常見(jiàn)的用途是防止除零錯(cuò)誤，例如：

SELECT 1 / NULLIF(0, 0) -- 1 / 0  FROM employees WHERE employee_id = 100;

示例中的 NULLIF 將第一個(gè)零轉(zhuǎn)換為 NULL，因此查詢(xún)結(jié)果返回 NULL；如果直接使用 1 / 0，查詢(xún)將會(huì)返回除零錯(cuò)誤。MySQL 中的除零錯(cuò)誤由 [sql_mode]變量控制。

NULLIF 函數(shù)同樣可以使用 CASE 表達(dá)式改寫(xiě)如下：

CASE WHEN exp1 = exp2 THEN NULL     ELSE exp1END

利用 CASE 表達(dá)式，我們還可以輕松實(shí)現(xiàn)多個(gè)值到 NULL 的轉(zhuǎn)換：

CASE WHEN expr IN (value1, value2, …)     THEN NULL     ELSE exprEND

> 📝COALESCE 和 NULLIF 函數(shù)實(shí)際上是 CASE 表達(dá)式的兩種縮寫(xiě)形式。

除了標(biāo)準(zhǔn) SQL 函數(shù)之外，數(shù)據(jù)庫(kù)還提供了一些專(zhuān)用的函數(shù)：

• Oracle 中的 NVL(expr1, expr2) 相當(dāng)于 2 個(gè)參數(shù)的 COALESCE。另外，NVL2(expr1, expr2, expr3) 如果第一個(gè)參數(shù)不為空，返回第二個(gè)參數(shù)的值；否則，返回第三個(gè)參數(shù)的值；

• MySQL 中的 IFNULL(expr1, expr2) 相當(dāng)于 2 個(gè)參數(shù)的 COALESCE。另外，IF(expr1, expr2, expr3) 如果第一個(gè)參數(shù)為真（expr1 <> 0 并且 expr1 不為空），返回第二個(gè)參數(shù)的值；否則，返回第三個(gè)參數(shù)的值；

• SQL Server 中的 ISNULL(expr1, expr2) 相當(dāng)于 2 個(gè)參數(shù)的 COALESCE。

字段約束與空值

如果不允許字段中存在未知或者缺失的數(shù)據(jù)，可以使用字段的 NOT NULL 約束。

對(duì)于唯一約束（UNIQUE），多個(gè) NULL 被看作是不同的值；因此，唯一約束字段中可以存在多個(gè)空值。不過(guò) SQL Server 是個(gè)例外：

CREATE TABLE t_unique(id INT UNIQUE);INSERT INTO t_unique VALUES(1);INSERT INTO t_unique VALUES(NULL);INSERT INTO t_unique VALUES(NULL); -- SQL Server 產(chǎn)生唯一鍵沖突錯(cuò)誤-- SQL Server 除外SELECT * FROM t_unique;    id|------|[NULL]|[NULL]|     1|-- SQL Server 除外SELECT * FROM t_unique;    id|------|[NULL]|     1|

對(duì)于 SQL Server 而言，唯一約束中只允許存在一個(gè) NULL 數(shù)據(jù)；所以第 3 個(gè) INSERT 語(yǔ)句執(zhí)行出錯(cuò)，最終只有兩條記錄。

如果是復(fù)合索引，情況略有不同：

CREATE TABLE t_unique2(c1 INT, c2 INT, UNIQUE(c1,c2));INSERT INTO t_unique2 VALUES(1, 1);INSERT INTO t_unique2 VALUES(NULL, NULL);INSERT INTO t_unique2 VALUES(NULL, NULL); -- SQL Server 產(chǎn)生唯一鍵沖突錯(cuò)誤INSERT INTO t_unique2 VALUES(1, NULL);INSERT INTO t_unique2 VALUES(1, NULL); -- Oracle 和 SQL Server 產(chǎn)生唯一鍵沖突錯(cuò)誤

其中，SQL Server 只允許有一個(gè)記錄的全部索引字段為空；如果某個(gè)字段不為空，Oracle 和 SQL Server 只允許有一個(gè)記錄的其他索引字段為空。

另外，檢查約束（CHECK）對(duì)于 NULL 的處理與 WHERE 條件正好相反：只要數(shù)據(jù)的檢查結(jié)果不是假都可以插入成功。例如：

CREATE TABLE t_check (  c1 INT CHECK (c1 >= 0),  c2 INT CHECK (c2 >= 0),  CHECK (c1 + c2 <= 100));INSERT INTO t_check VALUES (5, 5);INSERT INTO t_check VALUES (NULL, NULL);INSERT INTO t_check VALUES (200, NULL);SELECT * FROM t_check;c1    |c2    |------|------|     5|     5|[NULL]|[NULL]|   200|[NULL]|

如果 c1 和 c2 都有值的話(huà)，都必須大于等于零并且和值小于等于 100；c1 和 c2 都可以為空；如果其中之一為空，另一個(gè)字段的值可以大于 100。