SQL 是一種聲明式的語言，我們只需要描述想要的結果（WHAT），而不關心數據庫如何實現（HOW）；雖然 SQL 比較容易學習，但是仍然有一些容易混淆和出錯的概念。
今天我們就來說說 SQL 中的空值陷阱和避坑方法，涉及的數據庫包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。還是老規矩，結論先行：
NULL 即是空
在數據庫中，空值（NULL）是一個特殊的值，通常用于表示缺失值或者不適用的值。比如，填寫問卷時不愿意透露某些信息會導致錄入項的缺失，在公司的組織結構中總會有一個人（董事長/總經理）沒有上級領導。
首先一點，空值與數字 0 并不相同。假如我問你：你的錢包里有多少錢？如果你知道里面沒有錢，可以說是零；如果你不確定，那么就是未知，但不能說沒有。當我們需要創建一個表來存儲這個信息的時候，應該是 NULL；除非我們能夠確定錢包里面沒有錢或者有多少錢。
另外，空值與空字符串（''）也不相同，原因和上面類似。但是 Oracle 是一個例外，我們會在下文具體討論。
在大多數編程語言中，訪問 null 值通常會導致錯誤；但是 SQL 不會出錯，只是會影響到運算的結果而已。
三值邏輯
在大多數編程語言中，邏輯運算的結果只有兩種情況，不是真（True）就是假（False）。但是對于 SQL 而言，邏輯運算還可能是未知（Unknown）：
引入三值邏輯主要是為了支持 NULL，因為 NULL 代表的是未知數據。因此，SQL 中的邏輯運算與（AND）、或（OR）以及非（NOT）的結果如下：
對于 AND 運算符而言，真和未知的與運算有可能是真，也有可能是假；因此，最終的結果是未知。
> 📝SQL 中的 WHERE、HAVING 以及 CASE WHEN 子句只返回邏輯運算結果為真的數據，不返回結果為假或未知的數據。
空值比較
當我們使用比較運算符（=、<>、<、> 等）與 NULL 進行比較時，結果既不是真也不是假，而是未知；因為 NULL 表示未知，也就意味著可能是任何值。以下運算的結果都是未知：
NULL = 0NULL <> 0NULL <= 0NULL = NULLNULL != NULL
NULL 與任何值都不相等，甚至兩個 NULL 也不相等；因為我們不能說兩個未知的值相同，也不能說它們不相同。
> ⚠️對于比較運算而言，NULL 和 NULL 不相同；但是某些 SQL 子句中的 NULL 值被看作相同的值，例如 GROUP BY。具體參考下文。
那么，如何判斷一個值是否是 NULL 呢？為此，SQL 引入了兩個謂詞（WHERE 子句）：IS NULL和IS NOT NULL。以下示例用于查找 manager 為空的員工：
-- 使用比較運算符判斷空值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 | |
其中，第一個查詢使用比較運算符判斷空值，不會返回任何結果；第二個查詢使用 IS NULL 判斷空值，返回了正確的結果。
除了標準的 IS [NOT] NULL 之外，還有一些數據庫擴展的運算符可以用于空值比較：
-- 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 中的<=>可以用于等值比較，支持兩個 NULL 值；PostgreSQL 中的IS [NOT] DISTINCT FROM可以用于等值比較，支持兩個 NULL 值。
以下查詢的結果也不會返回任何結果：
SELECT employee_id, first_name, last_name, manager_id FROM employees WHERE (1 = NULL) OR (1 != NULL);
因為根據上面的三值邏輯，兩個未知結果的 OR 運算最終還是未知。
前文我們說過，空字符串不是 NULL；但是 Oracle 中的空字符串被看作 NULL。例如：
-- OracleSELECT 1 FROM dual WHERE '' IS NULL;VAL|---| 1|
-- 其他數據庫SELECT 1 AS val WHERE '' IS NULL;val|---|
當然，我們如果使用等值（=）運算符判斷空字符串與 NULL，結果仍然為空。
NOT IN 與空值
對于 WHERE 條件中的 IN 和 NOT IN 運算符，使用的是等值比較。所以如果 NOT IN 碰到了 NULL 值，永遠不會返回任何結果。例如：
SELECT employee_id, first_name, last_name, manager_id FROM employees WHERE 1 NOT IN (NULL, 2);
因為上面的條件實際上等價于：
SELECT employee_id, first_name, last_name, manager_id FROM employees WHERE 1 != NULL AND 1 != 2;
1 不等于 NULL 的結果是未知，1 不等于 2 的結果是真，未知和真的 AND 運算結果還是未知。
> ⚠️如果使用 NOT IN，一定要確保括號中的值不會出現 NULL；或者盡量使用 NOT EXISTS。
函數與空值
一般來說，函數和表達式的參數中如果存在 NULL，其結果也是 NULL。當然也有一些例外，比如聚合函數。
以下查詢返回的都是 NULL：
SELECT ABS(NULL), 1 + NULL FROM employees WHERE employee_id = 100;ABS(NULL)|1 + NULL|---------|--------| [NULL]| [NULL]|
一個未知值的絕對值仍然未知，1 加上一個未知值結果還是未知。
但是一個常見的例外是字符串與 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 看作空字符串，所以查詢結果為“Hello”；SQL Server 和 PostgreSQL 雖然區分了 NULL 和空字符串，但是 CONCAT 函數中這兩者等價；MySQL 中 NULL 參數導致 CONCAT 函數結果為 NULL；SQLite 沒有提供 CONCAT 函數。
另外，Oracle 中的 || 也將 NULL 看作空字符串；其他數據庫 || 中的 NULL 將參數會產生 NULL 結果；SQL Server 中使用 + 連接字符串，NULL 參數將會產生 NULL 結果。
聚合函數（SUM、COUNT、AVG 等）通常會在進行計算之前刪除 NULL 數據：
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|
第一個 SUM 函數返回的是 salary 和 commission_pct 都不為空的數據總和；第二個 SUM 函數返回的是 salary 不為空的數據總和加上 commission_pct 不為空的數據總和，所以比第一個數據大；COUNT 函數結果顯示 salary 有 107 條記錄不為空，commission_pct 只有 35 條記錄不為空。
如果輸入數據都是 NULL 值，除了 COUNT 函數之外的其他聚合函數返回 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(*) 函數總是返回所有數據的行數，不受空值的影響；COUNT(commission_pct) 返回了 0；AVG 和 SUM 函數返回了 NULL。
DISTINCT、GROUP BY、UNION 與空值
SQL 中的分組操作將所有的 NULL 值分到同一個組，包括 DISTINCT、GROUP BY 以及窗口函數中的 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 為空的數據有 72 條，但是分組之后只有一個 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，查詢結果將會保留 2 個 NULL 值。
ORDER BY 與空值
SQL 標準沒有定義 NULL 值的排序順序，但是為 ORDER BY 定義了 NULLS FIRST 和 NULLS LAST 選項，用于明確指定空值排在其他數據之前或者之后。
不同數據庫對此提供了不同的實現：
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 默認將 NULL 作為最大值，升序時排在最后；MySQL、SQL Server 和 SQLite 默認將 NULL 作為最小值，升序時排在最前。
另外，Oracle、PostgreSQL 和 SQLite 提供了擴展的 NULLS FIRST 和 NULLS LAST 選項：
-- 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 表達式實現類似的效果。以下示例與 NULLS LAST 作用相同，而且所有數據庫都可以使用：
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 表達式將 manager_id 為空的數據轉換為 1，非空的數據轉換為 0，所以空值排在其他數據之后；第二個排序字段 manager_id 確保了非空的數據從小到大排序。
空值處理函數
由于空值的特殊性，我們在分析數據時經常需要進行空值和其他值的轉換。為此，SQL 提供了兩個標準的空值函數：COALESCE 和 NULLIF。
COALESCE(exp1, exp2, ...) 函數用于將 NULL 轉換為其他值。當 exp1 不為空時返回 exp1，否則檢查 exp2；如果 exp2 不為空時返回 exp2，以此類推。例如：
SELECT COALESCE(NULL, NULL, 3) FROM employees WHERE employee_id = 100;COALESCE(NULL, NULL, 3)|-----------------------| 3|
由于前面兩個參數都是 NULL，COALESCE 最終返回了 3。
COALESCE 函數也可以使用 CASE 表達式改寫如下：
CASE WHEN exp1 IS NOT NULL THEN exp1 WHEN exp2 IS NOT NULL THEN exp2 ... ELSE expNEND
NULLIF(exp1, exp2) 函數用于將指定值轉換為 NULL。當 exp1 等于 exp2 時，返回 NULL；否則，返回 exp1 。NULLIF 最常見的用途是防止除零錯誤，例如：
SELECT 1 / NULLIF(0, 0) -- 1 / 0 FROM employees WHERE employee_id = 100;
示例中的 NULLIF 將第一個零轉換為 NULL，因此查詢結果返回 NULL；如果直接使用 1 / 0，查詢將會返回除零錯誤。MySQL 中的除零錯誤由 [sql_mode]變量控制。
NULLIF 函數同樣可以使用 CASE 表達式改寫如下：
CASE WHEN exp1 = exp2 THEN NULL ELSE exp1END
利用 CASE 表達式，我們還可以輕松實現多個值到 NULL 的轉換：
CASE WHEN expr IN (value1, value2, …) THEN NULL ELSE exprEND
> 📝COALESCE 和 NULLIF 函數實際上是 CASE 表達式的兩種縮寫形式。
除了標準 SQL 函數之外，數據庫還提供了一些專用的函數：
Oracle 中的 NVL(expr1, expr2) 相當于 2 個參數的 COALESCE。另外，NVL2(expr1, expr2, expr3) 如果第一個參數不為空，返回第二個參數的值；否則，返回第三個參數的值；
MySQL 中的 IFNULL(expr1, expr2) 相當于 2 個參數的 COALESCE。另外，IF(expr1, expr2, expr3) 如果第一個參數為真（expr1 <> 0 并且 expr1 不為空），返回第二個參數的值；否則，返回第三個參數的值；
SQL Server 中的 ISNULL(expr1, expr2) 相當于 2 個參數的 COALESCE。
字段約束與空值
如果不允許字段中存在未知或者缺失的數據，可以使用字段的 NOT NULL 約束。
對于唯一約束（UNIQUE），多個 NULL 被看作是不同的值；因此，唯一約束字段中可以存在多個空值。不過 SQL Server 是個例外：
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 產生唯一鍵沖突錯誤-- SQL Server 除外SELECT * FROM t_unique; id|------|[NULL]|[NULL]| 1|-- SQL Server 除外SELECT * FROM t_unique; id|------|[NULL]| 1|
對于 SQL Server 而言，唯一約束中只允許存在一個 NULL 數據；所以第 3 個 INSERT 語句執行出錯，最終只有兩條記錄。
如果是復合索引，情況略有不同：
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 產生唯一鍵沖突錯誤INSERT INTO t_unique2 VALUES(1, NULL);INSERT INTO t_unique2 VALUES(1, NULL); -- Oracle 和 SQL Server 產生唯一鍵沖突錯誤
其中，SQL Server 只允許有一個記錄的全部索引字段為空；如果某個字段不為空，Oracle 和 SQL Server 只允許有一個記錄的其他索引字段為空。
另外，檢查約束（CHECK）對于 NULL 的處理與 WHERE 條件正好相反：只要數據的檢查結果不是假都可以插入成功。例如：
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 都有值的話，都必須大于等于零并且和值小于等于 100；c1 和 c2 都可以為空；如果其中之一為空，另一個字段的值可以大于 100。
該文章在 2024/4/10 0:27:56 編輯過
400 186 1886
