狠狠色丁香婷婷综合尤物/久久精品综合一区二区三区/中国有色金属学报/国产日韩欧美在线观看 - 国产一区二区三区四区五区tv

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

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

NULL 即是空

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

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

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

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

三值邏輯

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

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

對于 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)行比較時，結(jié)果既不是真也不是假，而是未知；因為 NULL 表示未知，也就意味著可能是任何值。以下運(yùn)算的結(jié)果都是未知：

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

NULL 與任何值都不相等，甚至兩個 NULL 也不相等；因為我們不能說兩個未知的值相同，也不能說它們不相同。

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

那么，如何判斷一個值是否是 NULL 呢？為此，SQL 引入了兩個謂詞（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     |          |

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

除了標(biāo)準(zhǔn)的 IS [NOT] NULL 之外，還有一些數(shù)據(jù)庫擴(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 中的<=>可以用于等值比較，支持兩個 NULL 值；PostgreSQL 中的IS [NOT] DISTINCT FROM可以用于等值比較，支持兩個 NULL 值。

以下查詢的結(jié)果也不會返回任何結(jié)果：

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

因為根據(jù)上面的三值邏輯，兩個未知結(jié)果的 OR 運(yùn)算最終還是未知。

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

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

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

NOT IN 與空值

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

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 的結(jié)果是未知，1 不等于 2 的結(jié)果是真，未知和真的 AND 運(yùn)算結(jié)果還是未知。

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

函數(shù)與空值

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

以下查詢返回的都是 NULL：

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

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

但是一個常見的例外是字符串與 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 看作空字符串，所以查詢結(jié)果為“Hello”；SQL Server 和 PostgreSQL 雖然區(qū)分了 NULL 和空字符串，但是 CONCAT 函數(shù)中這兩者等價；MySQL 中 NULL 參數(shù)導(dǎo)致 CONCAT 函數(shù)結(jié)果為 NULL；SQLite 沒有提供 CONCAT 函數(shù)。

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

聚合函數(shù)（SUM、COUNT、AVG 等）通常會在進(jìn)行計算之前刪除 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|

第一個 SUM 函數(shù)返回的是 salary 和 commission_pct 都不為空的數(shù)據(jù)總和；第二個 SUM 函數(shù)返回的是 salary 不為空的數(shù)據(jù)總和加上 commission_pct 不為空的數(shù)據(jù)總和，所以比第一個數(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 值分到同一個組，包括 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 條，但是分組之后只有一個 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，查詢結(jié)果將會保留 2 個 NULL 值。

ORDER BY 與空值

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

不同數(shù)據(jù)庫對此提供了不同的實現(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 作為最大值，升序時排在最后；MySQL、SQL Server 和 SQLite 默認(rèn)將 NULL 作為最小值，升序時排在最前。

另外，Oracle、PostgreSQL 和 SQLite 提供了擴(kuò)展的 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 表達(dá)式實現(xiàn)類似的效果。以下示例與 NULLS LAST 作用相同，而且所有數(shù)據(jù)庫都可以使用：

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ù)之后；第二個排序字段 manager_id 確保了非空的數(shù)據(jù)從小到大排序。

空值處理函數(shù)

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

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

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

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

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

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 時，返回 NULL；否則，返回 exp1 。NULLIF 最常見的用途是防止除零錯誤，例如：

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

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

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

CASE WHEN exp1 = exp2 THEN NULL     ELSE exp1END

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

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

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

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

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

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

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

字段約束與空值

如果不允許字段中存在未知或者缺失的數(shù)據(jù)，可以使用字段的 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 產(chǎn)生唯一鍵沖突錯誤-- SQL Server 除外SELECT * FROM t_unique;    id|------|[NULL]|[NULL]|     1|-- SQL Server 除外SELECT * FROM t_unique;    id|------|[NULL]|     1|

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

如果是復(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)生唯一鍵沖突錯誤INSERT INTO t_unique2 VALUES(1, NULL);INSERT INTO t_unique2 VALUES(1, NULL); -- Oracle 和 SQL Server 產(chǎn)生唯一鍵沖突錯誤

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

另外，檢查約束（CHECK）對于 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 都有值的話，都必須大于等于零并且和值小于等于 100；c1 和 c2 都可以為空；如果其中之一為空，另一個字段的值可以大于 100。