結構化查詢語言/空值和三值邏輯

問題

如本華夏公益教科書的前一章和維基百科中所述，有時一行中的一列沒有值，或者換句話說，該列儲存的是空值標記（一個表示沒有資料的標記），或者使用 SQL 標準的概念，該列儲存空值。此空值標記與數值零或長度為零個字元的字串完全不同！通常，當應用程式尚未在該行的列中儲存任何內容時，就會出現這種情況。

（對 Oracle 使用者的提示：對於 Oracle，空值標記與長度為零個字元的字串相同。）

空值標記的存在引入了一個新的基本問題。在通常的布林邏輯中，存在兩個邏輯值 TRUE 和 FALSE。每個比較都評估為這兩個值中的一個，並且比較的否定評估為相反的值。如果一個比較評估為 TRUE，則其否定評估為 FALSE，反之亦然。例如，在通常的布林邏輯中，以下兩個比較之一為 TRUE，另一個為 FALSE：'x < 5'，'x >= 5'。

現在想象 x 現在持有空值標記的新情況。說 'NULL < 5' 為真 (1) 是不可行的。但我們可以說 'NULL < 5' 為假 (2) 並且其否定 'NULL >= 5' 為真 (3) 嗎？(3) 比 (1) 更可行嗎？當然不是。(1) 和 (3) 具有相同的“真實程度”，因此它們應該評估為相同的值。並且此值必須與 TRUE 和 FALSE 不同。

因此，通常的布林邏輯透過第三個邏輯值擴充套件。它被命名為UNKNOWN。根據定義，所有與空值標記的比較都導致此新值。並且眾所周知的語句“如果一個語句為真，則其否定為假”會丟失，因為存在第三種選擇。

SQL 的邏輯是這種所謂的三值、三元或三值邏輯 (3VL)的實現。SQL 中空值標記的存在並非沒有爭議。但如果接受空值，則 3VL 是必要的。

此頁面分兩個階段進行：首先，它解釋了與比較、分組等相關的空值的處理方式。其次，它解釋了新值 UNKNOWN 與任何其他布林值（包括自身）互動時的布林邏輯。

示例表

為了演示空值行為，我們定義了示例表：t1 和 t2。

CREATE TABLE t1 (
  id    DECIMAL PRIMARY KEY,
  col_1 DECIMAL,
  col_2 VARCHAR(20),
  col_3 DECIMAL
);

INSERT INTO t1 VALUES ( 1,  1,    'Hello World', 1);
INSERT INTO t1 VALUES ( 2,  2,    NULL,          2);
INSERT INTO t1 VALUES ( 3,  3,    'Hello World', NULL);
INSERT INTO t1 VALUES ( 4,  4,    'Hello World', NULL);
INSERT INTO t1 VALUES ( 5,  5,    'Hello Their', NULL);
INSERT INTO t1 VALUES ( 6,  NULL, 'Hello World', NULL);
INSERT INTO t1 VALUES ( 7,  NULL, 'Hello World', NULL);
INSERT INTO t1 VALUES ( 8,  8,    'Hello World', NULL);
INSERT INTO t1 VALUES ( 18, 18,   'Hello World', NULL);

CREATE TABLE t2 (
  id DECIMAL PRIMARY KEY,
  col_x DECIMAL
);

INSERT INTO t2 VALUES ( 1, 1 );
INSERT INTO t2 VALUES ( 2, NULL );
INSERT INTO t2 VALUES ( 3, 3 );
INSERT INTO t2 VALUES ( 4, 4 );
INSERT INTO t2 VALUES ( 5, 5 );
INSERT INTO t2 VALUES ( 18, 18 );
COMMIT;

步驟 1：空值的評估

比較謂詞，IS NULL 謂詞

SQL 認識六個比較謂詞 <，<=，=，>=，> 和 <>（不等於）。它們的主要目的是對數值進行算術比較。它們中的每一個都需要兩個變數或常量 (中綴表示法)。這意味著一個或兩個運算元都可能持有空值標記。如前所述，常見且非常簡單的規則是：“所有與空值標記的比較根據定義都會導致此新值 (unknown)。”以下是一些示例

NULL = 5 評估為 UNKNOWN。
5 = NULL 評估為 UNKNOWN。
NULL <= 5 評估為 UNKNOWN。
col_1 = 5 評估為 UNKNOWN（對於 col_1 持有空值標記的行）。
col_1 = col_2 評估為 UNKNOWN（對於 col_1 或 col_2 持有空值標記的行）。
NULL = NULL 評估為 UNKNOWN。
col_1 = col_2 評估為 UNKNOWN（對於 col_1 和 col_2 持有空值標記的行）。

WHERE 子句返回那些評估為 TRUE 的行。它不返回那些評估為 FALSE 或 UNKNOWN 的行。因此，不能保證以下 SELECT 會返回完整的表 t1

-- This SELECT will not return such rows where col_1 holds the NULL marker.
SELECT *
FROM   t1
WHERE  col_1 > 5
OR     col_1 = 5
OR     col_1 < 5;

當然，存在必須檢索包含空值標記的行的用例。由於算術比較無法做到這一點，因此必須使用另一種語言結構。這就是IS NULL 謂詞。

-- This SELECT will return exactly these rows where col_1 holds the NULL marker.
SELECT *
FROM   t1
WHERE  col_1 IS NULL;

其他謂詞

對於其他謂詞，沒有簡單的經驗法則。必須逐個解釋它們。

IN 謂詞是 OR 運算序列的快捷方式

只有兩個比較 'col_1 = 3' 和 'col_1 = 18' 才能檢索行（可能有多行）。比較 'col_1 = NULL' 永遠不會評估為 TRUE。它始終為 UNKNOWN，即使 col_1 持有空值標記也是如此。要檢索這些行，有必要（如上所示）使用 'IS NULL' 謂詞。

-- Shortcut for: col_1 = 3 OR col_1 = 18 OR col_1 = NULL
SELECT *
FROM   t1
WHERE  col_1 IN (3, 18, NULL);  -- the NULL case will never hit with the IN predicate!

這稍微複雜一些。這隻會返回 1、3 和 4，即 t2.col_x 或 t1.col_1 中沒有 NULL 的項。

SELECT *
FROM   t1
WHERE  col_1 IN (SELECT col_x FROM t2 WHERE id < 10);

EXISTS 謂詞的子查詢如果檢索到的行的基數大於 0，則評估為 TRUE，如果基數為 0，則評估為 FALSE。不可能出現 UNKNOWN 值。

-- The subselect to t2 can hit some rows - or not. If there are hits in the subselect, ALL rows of t1
-- are returned, else no rows of t1 are returned.
SELECT *                                 -- The select to table t1
FROM   t1
WHERE  EXISTS
       (SELECT * FROM t2 WHERE id < 10); -- The subselect to table t2

LIKE 謂詞將一列與正則表示式進行比較。如果該列包含空值標記，則 LIKE 謂詞返回 UNKNOWN 值，這意味著該行不會被檢索。

-- The LIKE retrieves only rows where col_2 matches the WHERE statement and col_2 is not NULL.
SELECT *
FROM   t1
WHERE  col_2 LIKE 'Hello %';

預定義函式

聚合函式COUNT(<column_name>), MIN(<column_name>), MAX(<column_name>), SUM(<column_name>) 和 AVG(<column_name>) 會忽略 <column_name> 包含空值標記的行。另一方面，COUNT(*) 會包含所有行。

如果標量函式（如 UPPER()、TRIM()、CONCAT()、ABS()、SQRT() 等）的引數包含空值標記，則結果值（在大多數情況下）是空值標記。

分組

在某些情況下，需要比較列值以確定它們是否不同。對於一般的數字和字串，這種比較的結果很明顯。但是，DBMS 如何處理 NULL 標記呢？它們彼此不同嗎，它們彼此相等嗎，還是根本沒有答案？為了獲得大多數使用者期望的結果，標準定義了“兩個 NULL 值不不同”，它們構成一個組。

SELECT DISTINCT col_1 FROM t1; 僅檢索 col_1 中包含 NULL 標記的所有行中的唯一一行。

... GROUP BY col_1 ...; 為 col_1 中包含 NULL 標記的所有行建立一個組。

步驟 2：三值邏輯 (3VL) 中的布林運算

在我們瞭解瞭如何對 NULL 標記進行各種比較和謂詞運算來生成 TRUE、FALSE 和 UNKNOWN 後，有必要解釋新邏輯值 UNKNOWN 的規則。

檢查

第一個基本操作是檢查一個真值：它是 TRUE、FALSE 還是 UNKNOWN？與 IS NULL 謂詞類似，還有三個附加謂詞

IS [NOT] TRUE
IS [NOT] FALSE
IS [NOT] UNKNOWN

-- Check for 'UNKNOWN'
SELECT *
FROM   t1
WHERE  (col_1 = col_3) IS UNKNOWN;  -- parenthesis are not necessary

-- ... is semantically equivalent to
SELECT *
FROM   t1
WHERE  col_1 IS NULL 
OR     col_3 IS NULL;

在邏輯系統的抽象語法中，p 代表任何真值。以下是三值邏輯真值表

p	IS TRUE	IS FALSE	IS UNKNOWN	IS NOT TRUE	IS NOT FALSE	IS NOT UNKNOWN
TRUE	TRUE	FALSE	FALSE	FALSE	TRUE	TRUE
FALSE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE
UNKNOWN	FALSE	FALSE	TRUE	TRUE	TRUE	FALSE

所有謂詞都導致 TRUE 或 FALSE，而不是 UNKNOWN。

NOT

下一個操作是對新值的否定。哪些值被評估為“NOT UNKNOWN”？UNKNOWN 值表示無法在 TRUE 和 FALSE 之間進行決策。不可能將這種不確定的否定導致 TRUE 或 FALSE。同樣，它是 UNKNOWN。

-- Which rows will match?  (1)
SELECT *
FROM   t1
WHERE  NOT col_2 = NULL; -- 'col_2 = NULL' evaluates to UNKNOWN in all cases, see above.

-- Is this SELECT equivalent to the first one?  (2)
SELECT *
FROM   t1
  EXCEPT
SELECT *
FROM   t1
WHERE  col_2 = NULL;

-- No, it's different!! Independent from NULL markers in col_2, (1) retrieves
-- absolutely NO row and (2) retrieves ALL rows.

上面的 SELECT (1) 將不檢索任何行，因為“NOT col_2 = NULL”與“col_2 = NULL”的評估結果相同，即 UNKNOWN。而 SELECT (2) 將檢索所有行，因為 EXCEPT 後的部分將不檢索任何行，因此僅與 EXCEPT 之前的部分相關。

在邏輯系統的抽象語法中，p 代表任何真值，而 NOT p 代表其否定。以下是適用的表格

p	NOT p
TRUE	FALSE
FALSE	TRUE
UNKNOWN	UNKNOWN

AND、OR

存在兩個二元運算 AND 和 OR。它們的評估方式如下

p	q	p AND q	p OR q
TRUE	TRUE	TRUE	TRUE
TRUE	FALSE	FALSE	TRUE
TRUE	UNKNOWN	UNKNOWN	TRUE
FALSE	TRUE	FALSE	TRUE
FALSE	FALSE	FALSE	FALSE
FALSE	UNKNOWN	FALSE	UNKNOWN
UNKNOWN	TRUE	UNKNOWN	TRUE
UNKNOWN	FALSE	FALSE	UNKNOWN
UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN

操作的優先順序按通常的方式定義：IS 謂詞、NOT、AND、OR。

一些示例

--
-- Add a new row to the test data base
INSERT INTO person (id, firstname, lastname)  -- Omit some columns to generate NULL markers
VALUES             (99, 'Tommy',   'Test');
COMMIT;

SELECT *
FROM   person
-- focus all tests to the new row
WHERE  id = 99                  -- (1): TRUE 
AND                             -- (3): (1) AND (2) ==> TRUE AND UNKNOWN ==> UNKNOWN
(
       date_of_birth = NULL     -- (2): UNKNOWN
);                              -- no hit

SELECT *
FROM   person
WHERE  id = 99                  -- (1): TRUE 
AND                             -- (3): (1) AND (2) ==> TRUE AND TRUE ==> TRUE
(
       date_of_birth IS NULL    -- (2): TRUE
);                              -- hit

SELECT *
FROM   person
WHERE  id = 99                  -- (1): TRUE 
OR                              -- (3): (1) OR (2) ==> TRUE OR UNKNOWN ==> TRUE
(
       date_of_birth = NULL     -- (2): UNKNOWN
);                              -- hit

SELECT *
FROM   person
WHERE  id = 99                  -- (1): TRUE 
AND                             -- (4): (1) AND (3) ==> TRUE AND FALSE ==> FALSE
(
NOT                             -- (3): NOT (2) ==> NOT TRUE ==> FALSE
       date_of_birth IS NULL    -- (2): TRUE

);                              -- no hit (same as AND date_of_birth IS NOT NULL)

-- Clean up the test database
DELETE FROM person WHERE id = 99;
DROP TABLE IF EXISTS t1, t2;     
COMMIT;

遞迴

結構化查詢語言
空值和三值邏輯

事務