為什麼SQL需要三值邏輯？剖析NULL值與UNKNOWN狀態的奧秘

Byadmin

為什麼SQL需要三值邏輯？剖析NULL值與UNKNOWN狀態的奧秘

您有沒有在撰寫 SQL 查詢時，碰到過那種讓人摸不著頭緒的結果？明明覺得條件都設對了，但查詢出來的資料卻不如預期，有時甚至出現一些難以解釋的 NULL 值，或是條件判斷似乎失靈了。嘿，別擔心，這絕對是不少 SQL 使用者都會遇到的「痛點」！其實，造成這種情況的根源，往往就藏在 SQL 底層一個非常核心的概念裡：三值邏輯 (Three-Valued Logic, 3VL)。

簡單來說，為什麼 SQL 需要三值邏輯？最直接的原因是為了能更精確、更全面地處理資料庫中「未知」或「不存在」的情況。傳統的布林邏輯只有「真」(TRUE) 和「假」(FALSE) 兩種結果，但真實世界的資料往往比這複雜得多。當我們在資料庫中遇到一個欄位的值是 NULL 時，它代表的可能不是「假」，而是「不知道」(UNKNOWN) 這個值是什麼。SQL 引入三值邏輯，就是為了能夠在條件判斷時，針對這三種狀態：TRUE、FALSE 和 UNKNOWN，都能給出一個明確的處理方式，從而避免了傳統二值邏輯在處理 NULL 時可能產生的邏輯混亂與錯誤。

在我的經驗裡，很多初學 SQL 的朋友，都會因為不理解 NULL 值的特性而寫出一些「怪怪」的查詢。例如，他們可能會覺得 `WHERE column = NULL` 應該會找出所有 NULL 值，但實際上，正確的寫法是 `WHERE column IS NULL`。這背後正是三值邏輯在作祟！當我們用 `column = NULL` 來判斷時，SQL 並不知道 `column` 的值是什麼，所以這個比較運算子的結果就是 UNKNOWN，也就無法滿足 WHERE 子句的條件，進而篩選不出任何資料。

傳統二值邏輯的侷限性

我們得先回頭看看，傳統的布林邏輯，也就是我們在數學和一般程式語言中常見的「真」和「假」，是如何運作的。這種邏輯非常直觀，一個敘述句（Statement）的結果，非真即假。例如，「今天天氣是晴天」這個敘述，如果是晴天，結果就是 TRUE；如果不是晴天，就是 FALSE。

在 SQL 的 `WHERE` 子句中，我們就是依賴這種布林邏輯來篩選資料。例如：

  • `WHERE age > 18`：如果某筆資料的 age 大於 18，這個條件就是 TRUE，該筆資料就會被選出來。
  • `WHERE status = ‘Active’`：如果某筆資料的 status 是 ‘Active’，條件就是 TRUE，資料被選出。

問題就出在，當資料庫中的某個欄位沒有值的時候，我們該怎麼辦？如果我們在一個欄位上設定了 NOT NULL 的約束，那沒問題，它一定會有值。但如果沒有，這個欄位的值就可能是 NULL。NULL 在 SQL 中，代表的是「遺失值」(Missing Value) 或「未知值」(Unknown Value)。它不是 0，也不是一個空的字串，它就是「沒有值」。

這就造成了傳統二值邏輯的斷點。當我們嘗試對一個 NULL 值進行比較運算時，例如 `NULL = 10`，結果是什麼？它是 TRUE 嗎？顯然不是，因為 NULL 不是 10。它是 FALSE 嗎？也不是，因為我們並不知道 NULL 到底是不是 10，它有可能等於 10，也有可能不等於。這種「既非真也非假」的狀態，就是傳統二值邏輯無法處理的。

SQL 的三值邏輯：TRUE, FALSE, UNKNOWN

為了妥善處理這種「未知」情況，SQL 擴展了邏輯系統，引入了第三種邏輯值：UNKNOWN。所以，SQL 的邏輯判斷結果，不再只有 TRUE 和 FALSE，還有 UNKNOWN。

這帶來了什麼影響呢？主要體現在以下幾個方面：

1. 比較運算子與 NULL 的互動

當任何一邊的運算元是 NULL 時，大部分的比較運算子（如 `=`, `<`, `>`, `<=`, `>=`, `<>`) 的結果都會是 UNKNOWN。

舉個例子：

  • `5 = 5` → TRUE
  • `5 = 10` → FALSE
  • `NULL = 5` → UNKNOWN
  • `NULL = NULL` → UNKNOWN (這個比較特別，因為 NULL 代表未知，兩個未知值之間也無法確定是否相等)
  • `10 > 5` → TRUE
  • `5 > 10` → FALSE
  • `NULL > 5` → UNKNOWN

2. 邏輯運算子 (AND, OR, NOT) 的行為

SQL 的邏輯運算子也必須適應這個三值邏輯。它們的運作方式如下：

  • AND：
    • TRUE AND TRUE → TRUE
    • TRUE AND FALSE → FALSE
    • FALSE AND TRUE → FALSE
    • FALSE AND FALSE → FALSE
    • TRUE AND UNKNOWN → UNKNOWN
    • UNKNOWN AND TRUE → UNKNOWN
    • FALSE AND UNKNOWN → FALSE (因為只要有 FALSE，整個 AND 運算結果就必定是 FALSE，不受 UNKNOWN 影響)
    • UNKNOWN AND FALSE → FALSE
    • UNKNOWN AND UNKNOWN → UNKNOWN

    AND 運算的關鍵在於：如果其中一個條件是 FALSE，整個結果就是 FALSE；如果其中一個是 TRUE，但另一個是 UNKNOWN，結果就變成了 UNKNOWN。

  • OR：
    • TRUE OR TRUE → TRUE
    • TRUE OR FALSE → TRUE
    • FALSE OR TRUE → TRUE
    • FALSE OR FALSE → FALSE
    • TRUE OR UNKNOWN → TRUE (因為只要有 TRUE，整個 OR 運算結果就必定是 TRUE，不受 UNKNOWN 影響)
    • UNKNOWN OR TRUE → TRUE
    • FALSE OR UNKNOWN → UNKNOWN
    • UNKNOWN OR FALSE → UNKNOWN
    • UNKNOWN OR UNKNOWN → UNKNOWN

    OR 運算的關鍵在於：如果其中一個條件是 TRUE，整個結果就是 TRUE；如果其中一個是 FALSE，但另一個是 UNKNOWN，結果就變成了 UNKNOWN。

  • NOT：
    • NOT TRUE → FALSE
    • NOT FALSE → TRUE
    • NOT UNKNOWN → UNKNOWN

    NOT 運算最直接，只有當面對 TRUE 或 FALSE 時有確定結果，面對 UNKNOWN 時，結果依然是 UNKNOWN。

3. `WHERE` 子句與 `HAVING` 子句

這應該是三值邏輯最常體現的地方了。在 `WHERE` 子句或 `HAVING` 子句中，只有結果為 TRUE 的條件才會被保留，篩選出對應的資料列。如果條件的結果是 FALSE 或 UNKNOWN，該資料列就不會被包含在結果集中。

這就解釋了為什麼 `WHERE column = NULL` 無法正常工作。當 `column` 是 NULL 時，`column = NULL` 的結果是 UNKNOWN，所以該列不會被選出。

正確處理 NULL 的方法：

  • 檢查 NULL：使用 `IS NULL` 或 `IS NOT NULL`。
    • `WHERE column IS NULL`：當 `column` 的值是 NULL 時，這個條件的結果就是 TRUE。
    • `WHERE column IS NOT NULL`：當 `column` 的值不是 NULL 時，這個條件的結果就是 TRUE。
  • 比較運算子與 NULL：如果需要判斷一個欄位是否不等於某個值，但該欄位可能是 NULL，那麼直接用 `<> NULL` 或 `!= NULL` 的話，結果會是 UNKNOWN，同樣無法篩選出資料。您需要考慮 NULL 的情況，例如：

    `WHERE (column <> ‘some_value’ OR column IS NULL)`

    這樣寫，如果 `column` 不是 `some_value`，第一個條件為 TRUE；如果 `column` 是 NULL，第二個條件為 TRUE。

4. `CASE` 語句

在 `CASE` 語句中，當條件判斷的結果是 UNKNOWN 時，它可能會跳過該 `WHEN` 子句，進入下一個 `WHEN` 或 `ELSE`。

例如，以下 SQL 語句： 

SELECT
    CASE
        WHEN column_a = 10 THEN 'Value is 10'
        WHEN column_a = 20 THEN 'Value is 20'
        ELSE 'Other value or NULL'
    END AS description
FROM
    your_table;

如果 `column_a` 是 NULL，`column_a = 10` 和 `column_a = 20` 這兩個條件的結果都會是 UNKNOWN。因此，這兩條 `WHEN` 子句都會被跳過，最終會執行 `ELSE` 部分，返回「Other value or NULL」。

5. `DISTINCT` 和 `GROUP BY`

在處理 `DISTINCT` 和 `GROUP BY` 時，SQL 對 NULL 值的處理方式是：將所有 NULL 值視為相等。這意味著，如果您有多個欄位的值都是 NULL，它們在 `GROUP BY` 中會被分到同一個群組；在 `DISTINCT` 查詢中，只會出現一個 NULL 值。

這點非常重要！如果您期待 `DISTINCT` 能區分不同的 NULL，那是不可能的。它把 NULL 當成一個「特殊值」，並且認為所有 NULL 都是同一個「特殊值」。

6. `ALL` 和 `ANY` (或 `SOME`) 運算子

雖然不那麼常用，但 `ALL` 和 `ANY` 運算子與三值邏輯的互動也值得一提。

  • `x > ALL (subquery)`：當子查詢中的任何一個值與 `x` 比較結果為 FALSE 或 UNKNOWN 時，整個運算結果就是 FALSE 或 UNKNOWN。只有當子查詢中的所有值都小於 `x` (結果為 TRUE) 時，整體結果才是 TRUE。
  • `x > ANY (subquery)`：當子查詢中的任何一個值與 `x` 比較結果為 TRUE 時，整個運算結果就是 TRUE。只有當子查詢中的所有值都大於或等於 `x` (結果為 FALSE 或 UNKNOWN) 時，整體結果才是 FALSE 或 UNKNOWN。

這些運算子的行為，在遇到 NULL 值時，會根據 AND/OR 的邏輯進行推導，可能會產生 UNKNOWN 結果，需要仔細分析。

實際應用與案例分析

理解了三值邏輯，我們就能更有效地解決許多實際問題。

案例一：找出所有未填寫 Email 的使用者

假設我們有一個 `users` 表，其中 `email` 欄位允許為 NULL。我們想找出所有沒有提供 Email 的使用者。

錯誤的寫法： 

SELECT user_id, user_name
FROM users
WHERE email = NULL;

如前所述，`email = NULL` 的結果是 UNKNOWN，所以不會篩選出任何資料。

正確的寫法： 

SELECT user_id, user_name
FROM users
WHERE email IS NULL;

`email IS NULL` 的結果在 `email` 為 NULL 時為 TRUE，這樣就能正確找出所有 Email 為 NULL 的使用者。

案例二：計算訂單總金額，但某些商品價格遺失

假設我們有一個 `order_items` 表，其中 `quantity` (數量) 和 `price` (單價) 欄位。我們想計算訂單的總金額，但有些商品的 `price` 可能遺失 (NULL)。

如果直接計算 `SUM(quantity * price)`，由於 `quantity * price` 在 `price` 是 NULL 時，結果也會是 UNKNOWN，SUM 函數在處理 UNKNOWN 時，通常會將其忽略 (類似於統計學上的忽略遺失值)。這聽起來好像沒什麼問題，但如果我們想更精確地知道有多少訂單項是「不確定的」，我們就需要注意。

我們可以這樣來處理： 

SELECT
    order_id,
    SUM(quantity * price) AS total_amount,
    COUNT(*) AS total_items,
    SUM(CASE WHEN price IS NULL THEN 1 ELSE 0 END) AS items_with_missing_price
FROM
    order_items
GROUP BY
    order_id;

在這個例子中，`CASE WHEN price IS NULL THEN 1 ELSE 0 END` 運用了三值邏輯。當 `price` 是 NULL 時，`price IS NULL` 為 TRUE，該項計為 1，表示有遺失價格。這能幫助我們識別出哪些訂單需要進一步檢查。

案例三：判斷使用者是否「活躍」

假設一個使用者被定義為「活躍」，如果他們的「最後登入時間」在過去 30 天內，或者他們的「帳戶狀態」是 ‘Active’。

讓我們看看這個 SQL 語句： 

SELECT user_id, user_name
FROM users
WHERE last_login_date >= DATE('now', '-30 days') OR account_status = 'Active';

現在，考慮 `last_login_date` 是 NULL 的情況。

  • 如果 `last_login_date` 是 NULL，那麼 `last_login_date >= DATE(‘now’, ‘-30 days’)` 這個條件的結果是 UNKNOWN。
  • 如果 `account_status` 是 ‘Active’，那麼 `account_status = ‘Active’` 這個條件結果是 TRUE。

整個 `WHERE` 子句會變成 `UNKNOWN OR TRUE`，根據 OR 的邏輯，結果是 TRUE。這意味著，即使最後登入時間是 NULL，只要帳戶狀態是 ‘Active’，使用者依然被認為是活躍的，這是符合預期的。

但如果 `account_status` 也不是 ‘Active’ (例如是 NULL 或 ‘Inactive’)，那麼 `WHERE` 子句就會變成 `UNKNOWN OR FALSE` (假設 `account_status = ‘Active’` 為 FALSE)，結果會是 UNKNOWN。

這也代表，如果 `last_login_date` 是 NULL 且 `account_status` 不是 ‘Active’，這個使用者將不會被選出來。這是否符合我們的定義？可能不一定。

更嚴謹的寫法，明確處理 NULL： 

SELECT user_id, user_name
FROM users
WHERE (last_login_date >= DATE('now', '-30 days') AND last_login_date IS NOT NULL)
   OR account_status = 'Active';

透過加上 `AND last_login_date IS NOT NULL`，我們確保了第一個條件只有在 `last_login_date` 有效且在 30 天內時才為 TRUE。這樣，當 `last_login_date` 是 NULL 時，第一個條件就變成了 `(UNKNOWN AND FALSE)`，結果是 FALSE。整體查詢就會變成 `FALSE OR account_status = ‘Active’`，更能精確地控制邏輯。

權威觀點與研究

SQL 標準本身就定義了三值邏輯。在 SQL 標準文件中，對於 NULL 的處理有非常詳盡的規定，這也促成了業界對於三值邏輯的普遍接受。C. J. Date，這位資料庫領域的權威人物，也曾多次強調資料模型與 SQL 應當遵循嚴謹的邏輯學原則，其中就包含了對 NULL 值及其所代表的「未知」狀態的精確處理。他認為，對 NULL 的正確理解和應用，是建立可靠和準確資料庫系統的基石。

學術界的研究也普遍認為，三值邏輯是處理不完整資訊 (Incomplete Information) 的標準方法，而資料庫中的 NULL 值正是這種不完整資訊的一種體現。因此，SQL 採用三值邏輯，是為了在模型層和查詢語言層，都能保持邏輯的一致性和數學上的嚴謹性。

總結：為何 SQL 非三值邏輯不可？

回到最初的問題：為什麼 SQL 需要三值邏輯？

答案是：為了能夠精確、無歧義地處理資料庫中「未知」(UNKNOWN) 的狀態，並以此為基礎，讓資料庫系統能夠進行有效的查詢、篩選、聚合與判斷。

傳統的二值邏輯 (TRUE/FALSE) 在面對資料庫中的 NULL 值時會失效，產生邏輯斷層。SQL 引入三值邏輯 (TRUE/FALSE/UNKNOWN)，是為了：

  • 精確處理 NULL： 讓比較運算子、邏輯運算子能夠在面對 NULL 時，產生符合邏輯的 UNKNOWN 結果，而不是錯誤的 TRUE 或 FALSE。
  • 嚴謹的查詢篩選： `WHERE` 和 `HAVING` 子句只接受 TRUE 結果，將 FALSE 和 UNKNOWN 的情況排除，確保了查詢結果的準確性。
  • 統一的 NULL 處理： `DISTINCT` 和 `GROUP BY` 將所有 NULL 視為同一類，提供了統一的處理機制。
  • 基於堅實邏輯： 為資料庫的操作建立在嚴謹的數學和邏輯學基礎之上。

作為 SQL 使用者，理解三值邏輯，尤其是 NULL 值在其中的特殊角色，是掌握 SQL、撰寫高效且準確查詢的關鍵。它不僅能幫助我們避免常見的陷阱，更能讓我們深入理解 SQL 的設計哲學，從而更游刃有餘地駕馭資料。

常見相關問題與專業詳細解答

Q1：為什麼我用 `WHERE column = NULL` 找不到資料？

這是一個最常見的問題，也是三值邏輯最直觀的體現。在 SQL 中，NULL 代表「未知」或「缺失」的值。當你寫 `column = NULL` 時，SQL 實際上是在執行一個比較運算：將 `column` 的值與 NULL 進行比較。由於 NULL 代表未知，我們無法確定 `column` 是否等於這個未知值，所以這個比較運算子的結果就是 **UNKNOWN**。

而 SQL 的 `WHERE` 子句只會保留結果為 **TRUE** 的資料列。既然 `column = NULL` 的結果是 UNKNOWN，而不是 TRUE，因此任何符合 `column = NULL` 條件的資料列都不會被篩選出來。

要正確地找出 NULL 值，你必須使用專門的運算子：`IS NULL`。例如，`WHERE column IS NULL`。當 `column` 的值確實是 NULL 時，`IS NULL` 這個運算子的結果就是 **TRUE**，這樣才能正確地篩選出所有 NULL 值的資料列。

Q2：NULL 和空字串 (”) 或 0 有什麼區別？

這個區別非常重要！NULL、空字串 (”) 和 0 在資料庫中代表的是完全不同的概念：

  • NULL： 代表「遺失」、「未知」或「不存在」的值。它不是一個具體的數值或字串，而是一個「狀態」。
  • 空字串 (”)： 代表一個「長度為零的字串」。它是一個具體的字串值，只是這個字串裡面沒有任何字元。
  • 0： 代表數值「零」。它是一個具體的數值。

舉個例子：

  • 在一個 `VARCHAR` 類型的欄位中，`NULL` 表示我們不知道這個人的名字是什麼，或者他沒有名字；而 `”` 表示我們知道他的名字，但名字是空的（例如，有些系統不允許名字為空，但可能會有這個情況）。
  • 在一個 `INT` 類型的欄位中，`NULL` 表示我們不知道這個人的年齡；而 `0` 表示這個人的年齡就是 0 歲。

在 SQL 查詢中，這三者之間的區別也體現在邏輯判斷上：

  • `WHERE column IS NULL` 找出 NULL 值。
  • `WHERE column = ”` 找出空字串。
  • `WHERE column = 0` 找出數值 0。

如果你試圖用 `column = NULL` 來判斷空字串或 0，結果一樣會是 UNKNOWN，因為 NULL 與任何具體值（包括空字串和 0）比較，結果都是 UNKNOWN。

Q3：在 `OR` 條件中使用 NULL 值時，該如何判斷？

在 `OR` 條件中使用 NULL 值時，情況會稍微複雜一些，但理解了三值邏輯的規則後就清晰了。回顧一下 `OR` 的邏輯：

  • TRUE OR TRUE → TRUE
  • TRUE OR FALSE → TRUE
  • FALSE OR TRUE → TRUE
  • FALSE OR FALSE → FALSE
  • TRUE OR UNKNOWN → TRUE
  • UNKNOWN OR TRUE → TRUE
  • FALSE OR UNKNOWN → UNKNOWN
  • UNKNOWN OR FALSE → UNKNOWN
  • UNKNOWN OR UNKNOWN → UNKNOWN

關鍵在於：如果 `OR` 運算式中有任何一個部分是 **TRUE**，那麼整個 `OR` 運算式的結果就是 **TRUE**，不受其他部分的影響（即使其他部分是 UNKNOWN）。但是，如果所有為 TRUE 的部分都為 FALSE，並且存在 UNKNOWN，那麼結果就是 **UNKNOWN**。

舉個例子： 

-- 假設 last_login_date, account_status 欄位可能為 NULL
WHERE last_login_date >= DATE('now', '-30 days') OR account_status = 'Active';

假設有三種情況：

  1. `last_login_date` 在 30 天內 (TRUE)，`account_status` 是 ‘Active’ (TRUE)。 結果： TRUE OR TRUE → TRUE。
  2. `last_login_date` 是 NULL (UNKNOWN)，`account_status` 是 ‘Active’ (TRUE)。 結果： UNKNOWN OR TRUE → TRUE。
  3. `last_login_date` 是 NULL (UNKNOWN)，`account_status` 不是 ‘Active’ (FALSE)。 結果： UNKNOWN OR FALSE → UNKNOWN。

在第 3 種情況下，由於 `last_login_date` 是 NULL，導致整個 `OR` 條件的結果為 UNKNOWN，這意味著該資料列不會被 `WHERE` 子句選中。這可能不是我們想要的結果，我們也許希望，即使 `last_login_date` 是 NULL，只要 `account_status` 不是 ‘Active’，也應該被選中（如果我們是想找出「非活躍」的使用者）。

因此，當你的 `OR` 條件中包含可能為 NULL 的欄位時，你通常需要更仔細地考慮，是否要額外加上 `IS NOT NULL` 的檢查，以確保邏輯的精確性，就像前面案例三的嚴謹寫法所示。

Q4：`SELECT DISTINCT` 或 `GROUP BY` 在處理 NULL 時有什麼特別之處？

是的，`DISTINCT` 和 `GROUP BY` 對 NULL 的處理方式非常獨特，它們會將所有的 NULL 值視為相等。

  • `SELECT DISTINCT`： 當你在一個欄位上使用 `DISTINCT`，而該欄位有多個 NULL 值時，結果集中只會出現一個 NULL 值。SQL 認為所有的 NULL 都是「同一個」NULL。
  • `GROUP BY`： 如果你使用 `GROUP BY column`，所有 `column` 值為 NULL 的資料列都會被分到同一個群組。這個群組在 SQL 中會被視為一個單獨的群體，與其他非 NULL 的群體區分開來。

這是一個很方便的特性，因為它提供了一種將所有「未知」或「遺失」的資料統一歸類的標準方式。例如，如果你想計算不同狀態的使用者數量，而狀態欄位可能包含 NULL，`GROUP BY status` 會為你產生一個包含 NULL 的群組，讓你統計出所有狀態未知的使用者數量。

這與 `column = NULL` 會產生 UNKNOWN 的行為是不同的。在比較運算中，NULL 與 NULL 比較結果是 UNKNOWN；但在分組和去重時，NULL 們卻被視為「相等」並被合併。這種雙重標準正是 SQL 設計中為了應對不同情境的靈活性。

為什麼SQL需要三值邏輯