結構化查詢語言/SELECT：IS NULL 謂詞

建立新行時，我們可能不知道一列或多列的值。

假設我們要儲存有關銀行賬戶的資訊，並且其中一個賬戶的餘額我們不知道。我們該怎麼辦？有幾種可能性

• 拒絕包含所有其他資訊的整行，例如賬戶號、可用信用額度、利率等。這不是很吸引人。
• 儲存預設值而不是我們當前不知道的值。但是，在某些情況下，不可能定義預設值，因為每個值都是可能的，例如，銀行賬戶為“0”或“-1”並不罕見。
• 儲存一個標誌，指示沒有儲存值。這種方法類似於非數字技術。

關係型資料庫管理系統使用最後提到的技術，標誌的意思是“沒有儲存值”。有時人們說“儲存了NULL值”或“儲存了NULL特殊標記”。

布林邏輯的擴充套件

假設有一個用於銀行賬戶的表格，其中一些行在餘額列中包含 NULL 特殊標記。這些行是否滿足“balance >= 0”或“balance <= 0”這兩個 WHERE 條件中的至少一個？不。無法確定這些條件是真還是假！老實說，我們必須承認，我們不知道我們通常的真/假邏輯中的答案，因為我們不知道餘額的值。我們被迫用第三個值擴展布爾值的範圍，我們稱之為未知。以上兩個條件既不為真也不為假；對於餘額包含 NULL 特殊標記的行，它們都將評估為“未知”。

在後面的階段，我們需要對布林運算子 NOT、AND、OR 和 EQUAL 進行定義，以便真/假與未知進行互動。您可以在此處找到定義。

檢索 NULL 特殊標記

在每個 SELECT 命令中，這樣的行將成為結果行的一部分，其中 WHERE 條件評估為真。如果評估為假或未知，則該行將被拒絕。由於上面所有類似“balance >= 0”的 WHERE 條件（以及它們的否定）對於缺少餘額值的行都將評估為未知，因此初步沒有辦法檢索它們。

為了克服這種不足，SQL 包含特定的短語“IS NULL”。短語“balance IS NULL”對餘額中缺少值的那些行精確評估為真。

SELECT ...
FROM   ...
WHERE  <columnname> IS NULL
...    
;

我們必須使用完全相同的措辭。使用任何算術運算子，如 >、<=、!= 等，都不會檢索包含 NULL 特殊標記的行。即使對於條件“（balance = 0）OR NOT（balance = 0）”，它也是傳統真/假邏輯中的重言式，也是如此。除了這個 IS NULL 謂詞之外，沒有其他方法可以檢索 NULL 特殊標記，除非有一個簡單但無用的例外：如果省略 WHERE 條件，則將檢索表格的所有行，包括任何列中包含和不包含 NULL 特殊標記的行。

就這樣！如果您第一次遇到此主題，處理 NULL 特殊標記和 3 值邏輯聽起來可能很奇怪。但是由於 IS NULL 謂詞始終評估為真或假，因此之後一切照常進行。我們可以使用 SELECT 命令的其他所有元素（布林邏輯、連線、having、order by 等），就像我們迄今為止所做的那樣。

一些示例

我們的測試資料庫不包含 NULL 特殊標記。然而，我們在解釋外連線期間遇到了這種情況。外連線會建立結果行，其中一些列包含 NULL 特殊標記。如果我們處理此類子查詢的結果，我們必須考慮這種可能性。

還有兩種方法可以生成 NULL 特殊標記。

• 使用 NULL 特殊標記的顯式表示法插入或更新命令。在這種情況下，SQL 關鍵字null用作 NULL 特殊標記的代表。
• 不使用所有列的 INSERT 命令。省略的列將獲得 NULL 特殊標記，或者獲得預設值（如果已定義）。

為了演示這一點併為以下練習建立一些示例，我們將一行放入person表格中，並留出一些列為空。

-- Insert a new row for testing purpose
INSERT INTO person (id, firstname, lastname)  VALUES (51, 'Half man', 'Uncomplete');
COMMIT;

-- Retrieve the row. As defined in CREATE TABLE statement the weight has a default value of integer 0.
-- Date_of_birth and place_of_birth contain the NULL special marker.
SELECT * FROM person WHERE  id = 51;

-- use the IS NULL predicate within WHERE clause. The result contains 1 row.
SELECT * FROM person WHERE ssn IS NULL;

-- weight has a value!! We expect to retrieve no rows when we use the IS NULL predicate.
SELECT * FROM person WHERE weight IS NULL;
-- or, to say it the other way round, the number of rows is 0
SELECT count(*) FROM person WHERE weight IS NULL;
-- but in the next statement the number of rows is 1
SELECT count(*) FROM person WHERE weight = 0;

-- Negate the IS NULL predicate
SELECT count(*) FROM person WHERE ssn IS NULL;     -- IS NULL
SELECT count(*) FROM person WHERE ssn IS NOT NULL; -- Negation of IS NULL

SELECT count(*)
FROM   person
WHERE  ssn IS NULL
OR     ssn IS NOT NULL; -- A tautology, which always retrieves ALL rows of a table
-- Same as above
SELECT count(*)
FROM   person
WHERE  ssn IS NULL
OR NOT ssn IS NULL; -- A tautology, which always retrieves ALL rows of a table

接下來，我們將展示 UPDATE 命令與關鍵字 NULL 結合使用的示例

--
-- Insert a new row for testing purpose with all columns filled with a useful value
INSERT INTO person (id, firstname, lastname,  date_of_birth,    place_of_birth, ssn,           weight)
VALUES             (52, 'Lyn',     'Mutable', DATE'1951-05-13', 'Anchorage',    '078-05-1152', 69);
COMMIT;
SELECT * FROM person WHERE id = 52;

-- Delete a single column value (not the complete row)
UPDATE person SET ssn = null WHERE id = 52;
COMMIT;
SELECT * FROM person WHERE id = 52;      -- one row
SELECT * FROM person WHERE ssn IS NULL;  -- two rows: 51 + 52

恢復示例資料庫的原始狀態。

DELETE FROM person WHERE id > 50;
COMMIT;

Coalesce() 和類似函式

在 NULL 特殊標記的上下文中，我們經常需要檢索沒有值（NULL 特殊標記）或預設值（如 0 或空白）的行。在這種情況下，WHERE 條件類似於“... WHERE (col IS NULL OR col = 0) ...”。為了使原始碼更簡單，SQL 標準定義了一個函式coalesce(<expression_1>, <expression_2>)。如果第一個引數（通常是列的名稱）不是 NULL，則該函式評估為該引數，否則評估為第二個引數。

示例

-- Retrieve rows without ssn or with ssn equal to blank.
SELECT *
FROM   person
WHERE  coalesce(ssn, ' ') = ' ';
-- equivalent:
-- WHERE (ssn IS NULL
-- OR     ssn = ' ');

函式名coalesce來自這樣一個事實，即該函式可以接受任意數量的引數並遞迴地評估它們。如果引數n導致真實值，則它評估為該引數，否則該函式將呼叫自身而不包含第 n 個引數。coalesce(expression_1, expression_2, expression_3) 評估為 expression_1，如果 expression_1 不是 NULL，否則評估為 expression_2，如果 expression_2 不是 NULL，否則評估為 expression_3。

SQL 標準定義了另一個函式nullif(<expression_1>, <expression_2>)。如果兩個表示式相等，則它評估為 NULL，如果它們彼此不同，則它評估為第一個表示式。

不同的供應商提供了一些其他函式，如isnull()、ifnull() 或nvl() 來支援處理 NULL 值。這些函式的含義是供應商特定的。

練習

插入一個新的愛好“雪鞋行走”，沒有備註。

點選檢視解決方案

INSERT INTO hobby (id, hobbyname,     remark)
VALUES            (10, 'Snowshoeing', null);
COMMIT;

找到上述問題的第二個解決方案，無需使用關鍵字“null”。（首先刪除第 10 行。）

點選檢視解決方案

DELETE FROM hobby WHERE id = 10;
INSERT INTO hobby (id, hobbyname)
VALUES            (10, 'Snowshoeing');
COMMIT;

檢索所有沒有備註的愛好。

點選檢視解決方案

-- 1 row
SELECT * FROM hobby WHERE remark IS NULL;

有多少愛好以備註的方式舉例說明？

點選檢視解決方案

-- 9 rows
SELECT count(*) FROM hobby WHERE remark IS NOT NULL;

更改第 10 行愛好，使愛好名稱包含字串“NULL”，備註包含“愛好名稱未知”。

點選檢視解決方案

-- Consider the two apostrophes surrounding the string 'NULL', which consists of the 4 characters N, U, L and L !!
UPDATE hobby SET hobbyname = 'NULL', remark = 'Name of hobby not known' WHERE id = 10;
COMMIT;

a) 檢索愛好名稱為“NULL”的行。
b) 檢索備註為“愛好名稱未知”的行。

點選檢視解決方案

-- This may be a pitfall question. There is no relation to the IS NULL predicate
SELECT * FROM hobby WHERE hobbyname = 'NULL';
SELECT * FROM hobby WHERE remark = 'Name of hobby not known';

有多少愛好有愛好名稱？

點選檢視解決方案

-- All 10 rows contains a hobby name, even the row with the hobbyname 'NULL'
SELECT count(*) FROM hobby WHERE hobbyname IS NOT NULL;