空值 (SQL)

空值符號來表示空值。在 SQL中則是以 NULL 用於標識空值的保留關鍵字。SQL null是一個狀態，而不是一個值。這種用法與大多數程式語言完全不同，其中參照的空值意味著不指向任何物件。這不應與 0 數值混淆。空值表示缺少值－而與零值不同，與缺乏答案的方式不同，作為“否”的答案。例如“亞當擁有多少本書？”這個問題，答案可能是“零”開始，並且在我們確定亞當沒有書籍之前，並不會更新為值“零”。資料庫表主鍵的取值不能為空值。另外，資料庫中的統計計算，一般將有空值的資料忽略不計。在SQL的Where條件式去判斷id欄位是否為Null時，像是 where id = null 是無法正確執行的，必須寫成 where id is null ；反之若要判斷為id欄位是否為非Null時，則用where id is not null* coalesce(n,e)被Oracle DB、Microsoft SQL Server、IBM DB2、Postgre SQL、MySQL等資料庫支援* isnull(n,e)被Microsoft SQL Server、Microsoft-Access等資料庫支援* nvl(n,e)被Oracle DB資料庫支援* Nz(n,e)被Microsoft-Access等資料庫支援歷史.EF Codd 在一篇 1975年的ACM - SIGMOD FDT報告的論文中提到了空值作為表示關係模型中缺失資料的一種方法。Codd最常被引用的與Null語義相關的論文現在被稱為“Codd tables”。 Codd後來強化了他的要求，即在 "ComputerWorld"雜誌上發表的一篇 1985年由兩部分組成的文章中，所有 RDBMS都支援 Null來表示缺失的資料。在 IBM System R中實現原型後，1986年的 SQL標準基本上採用了 Codd的建議。雖然 Don Chamberlin認為nulls。Chamberlin還認為，除了提供一些缺失值功能之外，對於空值的實踐經驗也會導致依賴於空值的其它語言特性，如某些分組構造和外連線。最後，他認為在實踐中，Nulls最終也會被用作快速修補現有模式的一種方法，當它需要超出其原始意圖的時候，不是為了丟失而是為了不適用的資料編碼; 例如，一個資料庫如果有每英里加侖的欄位，需要快速新增值為電動汽車時。Codd在其 1990年出版的“資料庫管理關係模型第2版”中指出，SQL標準所要求的單一 Null 型別是不夠的，應該用兩種不同的 Null 型別標記來替代，以表明資料丟失的原因。在 Codd的書中，這兩個 Null型標記分別被稱為“A值”和“I值”，分別表示“缺失但適用”和“缺失但不適用”。 Codd的建議將要求擴充套件 SQL的邏輯系統以適應四值邏輯系統。由於這種額外的複雜性，具有不同定義型別的多個空值概念，尚未在資料庫領域廣泛接受。儘管如此，它仍然是一個活躍的研究領域，許多論文還在發表。挑戰.由於其相關的三值邏輯，它在 SQL連線中使用的特殊要求，以及聚合函式和 SQL分組運算子所需的特殊處理，空值一直是爭議的焦點和爭議的電腦科學教授 Ron van der Meyden將各種問題總結為：“SQL標準中的不一致，意味著不可能將任何直觀的邏輯語義歸於 SQL中的空值處理”。 儘管為解決這些問題提出了各種建議，但候選方案的複雜性阻礙了它們的廣泛採用。空值的散播.由於 Null不是資料值，而是缺少值的標記，因此以數學運算子使用 Null會給出未知結果。算術運算.該結果由空值表示。下例中將 Null乘以 10將導致Null：10 * NULL -- Result is NULL這可能會有出乎意料的結果。例如，當試圖將 Null除以零時，平臺可返回 Null值而不會丟擲預期的“除以零－資料異常”。雖然這種行為不是由 ISO SQL標準定義的，但許多 DBMS供應商都以類似的方式處理這一操作。例如，Oracle，PostgreSQL，MySQL伺服器和 Microsoft SQL Server平臺都會針對以下內容返回空值結果：NULL / 0字串連線.在 SQL中常見的字串連線操作，在其中一個操作元為 Null時也會導致 Null。下例演示了使用 Null與 SQL 字串連線運算子 || 返回的 Null結果：'Fish ' || NULL || 'Chips' -- Result is NULL對於資料庫實作來說並非都是如此。在 Oracle RDBMS中，例如 NULL和空字串被認為是相同的，因此codice_1字串連線的結果是 'Fish晶片'。NULL和三值邏輯的比較.由於 Null不是任何資料域的成員，因此它不被視為“值”，而是指缺失值的標記。因此與 Null進行比較永遠不會導致 True或 False，而總變成在三值邏輯的 Unknown 結果中。下面表示式的邏輯結果是將值 10 與 Null進行比較，得到的結果會成為未知：SELECT 10 = NULL -- Results in Unknown但是，如果缺失值與操作結果無關，則對 Null的某些操作可以返回值。考慮下面的例子：SELECT NULL OR TRUE -- Results in True在這種情況下，OR 左邊的未知值這一事實是無關緊要的，因為 OR 的操作結果將是 True，而不管左邊的值如何。SQL實現了三值邏輯，因此 SQL的實作必須提供專門的三值邏輯WHERE子句中的未知效應.在資料操縱語言和查詢的比較謂詞中，若遇到 SQL三值邏輯，該 WHERE子句會導致 DML語句僅對那些謂詞評估為 True的列結果起作用；若是 INSERT，UPDATE 或者 DELETE 等 DML語句，則因為此謂詞計算的結果為假、或是未知列，不會執行，並放棄 SELECT查詢。將 Unknown 解釋成 False 相同的邏輯結果，是處理空值時會遇到的常見錯誤，下面的簡單例子說明瞭這個謬誤：SELECT *FROM tWHERE i = NULL;上例的查詢邏輯一定返回零列，因為 i 欄位與 Null 比較的結果一定是傳回“未知”，即使對於那些 i 為 Null 的資料列也是如此。未知結果導致 SELECT語句立即丟棄每一列。空值指定和3VL特定比較謂詞.基本 SQL比較運算子在與 Null進行比較時，始終返回“未知”，因此 SQL標準提供了兩個特定的 Null 比較謂詞： codice_2和 codice_3，來檢測資料是不是空值。SQL標準包含一個擴充套件 F571“真值測試”，它引入了三個額外的邏輯一元運算子，也使用了字尾表示法。他們有以下真值表：F571擴充套件與 SQL中布林資料型別的存在正交，儘管語法相似，但 F571不會在語言中引入布林值或三值文字。1999年，在布林型資料型別被引入標準之前，F571擴充套件實際上存在於 SQL92中。然而，F571擴充套件只有少數系統實作；PostgreSQL是實作它的資料庫之一。加入三值邏輯中的其它運算子使 SQL 的三值邏輯功用完善，這意味著它的邏輯運算子可以表示任何可能的三值邏輯函式。在不支援 F571擴充套件的系統上可以遍歷每種可能組合，使表示式 p Unknown的引數來模擬 IS UNKNOWN p，並使用 IS NULL或其他 NULL特定函式來測試這些引數，雖然這樣子可能更繁瑣。空值在其它構造式中的影響.CASE表示式.SQL提供了兩種風格的條件表示式。一為“簡單 CASE”並像 switch 語句一樣操作。另一在標準中稱為“搜尋 CASE”，並像 if ... elseif一樣執行。簡單 codice_4 表示式使用隱式相等比較，它們的執行和 DML的 codice_5 子句處理 Null 的規則是相同的。因此，一個簡單 codice_4 表示式無法直接檢查 Null 的存在。在簡單 codice_4 表示式中檢查 Null 總會導致結果為未知，如下所示：SELECT CASE i WHEN NULL THEN 'Is Null' -- This will never be returnedWHEN 0 THEN 'Is Zero' -- This will be returned when i = 0WHEN 1 THEN 'Is One' -- This will be returned when i = 1ENDFROM t;不管 i 列所包含的的值是什麼，該表示式的計算結果都為“未知” ，codice_8 字串將永遠不會返回。另一方面，“搜尋” codice_4表示式可以使用謂詞 codice_2 和 codice_3 條件。下例顯示如何使用搜索 codice_4表示式來正確檢查 Null：SELECT CASE WHEN i IS NULL THEN 'Null Result' -- This will be returned when i is NULLWHEN i = 0 THEN 'Zero' -- This will be returned when i = 0WHEN i = 1 THEN 'One' -- This will be returned when i = 1ENDFROM t;Oracle的 SQL 方言提供了一個內建函式 codice_13，可以用它來代替簡單的 CASE表示式，並考慮兩個相等的空值。SELECT DECODE(i, NULL, 'Null Result', 0, 'Zero', 1, 'One') FROM t;如果最後都沒有與條件相匹配的結果，所有這些結構將會返回 NULL；它們有一個預設的 codice_14 子句。程式擴充套件中的 IF語句.SQL/PSM定義 SQL的預存程式擴充套件，例如 IF 語句。但歷史上主要的 SQL供應商都包含了他們自己專有的程式擴充套件。迴圈和比較的程式擴充套件在空值比較規則下執行，類似於 DML 語句和查詢。以下預存程式的片段以 ISO SQL 標準格式演示了在 IF 語句中使用 3VL的 Null 。IF i = NULL THENSELECT 'Result is True'ELSEIF NOT(i = NULL) THENSELECT 'Result is False'ELSESELECT 'Result is Unknown';該IF語句僅對那些評估為 True 的比較執行操作。對於評估為 False 或未知的 IF 語句，該語句將控制傳遞給 ELSEIF 子句，最後傳遞給 ELSE 子句。上面程式碼將因與 Null 的比較始終評估為未知，結果一定會是 codice_15 。外連線.SQL 的外連線、左外連線和右外連線，會自動生成空值以作為結果表中缺失值的佔位符。如對於左外連線會產生空值，代替左外連線操作後，右側表中欄位缺少值的列。下例使用兩個表來示範左外連線操作產生的空值佔位符。第一個表包含相關的員工編號和電話號碼，如下。以下示例 SQL查詢對這兩個表執行左外連線。SELECT e.ID, e.LastName, e.FirstName, pn.NumberFROM Employee eLEFT OUTER JOIN PhoneNumber pnON e.ID = pn.ID;此查詢生成的結果集演示 SQL如何使用 Null作為右側表中缺少的值的佔位符，如下所示。聚合函式.SQL定義了聚合函式以簡化伺服器端的資料聚合計算。除 codice_16函式外，所有集合函式都會執行 Null-elimination 步驟，因此計算的最終結果中不包含 Null。請注意，消除 Null不等於用零替換 Null。例如下表中 codice_17將給出與 codice_19不同的結果：這裡的 codice_17 是 200。在 SQL 聚合函式中，codice_22不等於 codice_23，這是一個眾所周知的副作用。聚合函式的輸出也可以是空值。這裡是一個例子：SELECT COUNT(*), MIN(e.Wage), MAX(e.Wage)FROM Employee eWHERE e.LastName LIKE '%Jones%';此查詢將始終輸出一行，計算姓氏中包含“Jones”的員工人數，並給出這些員工的最低和最高工資。但是，如果沒有員工符合給定的標準會發生什麼？計算空集的最小值或最大值是不可能的，所以這些結果必須為NULL，表示沒有答案。這不是未知值，它是表示沒有值的空值。結果是：當兩個空值相等時：分組，排序和一些設定操作.由於 SQL 2003將所有 Null標記定義為彼此不相等，因此需要特殊定義才能在執行某些操作時將 Null分組在一起。SQL將“任何兩個彼此相等的值或任何兩個空值”定義為“沒有不同”。這允許 SQL在使用 GROUP BY 子句時對 Null 進行分組和排序。其他 SQL操作，子句和關鍵字在處理空值時使用“沒有不同”。這些包括以下內容：SQL規範中的 UNION運算子效果會違反空值不相等的原則，透過論證刪除集合操作中的重複項“在比檢索操作的評估中的等式測試更低的細節層次上發生”這種語義不一致性是合理的。SQL標準沒有明確定義空值的預設排序順序。相反，在符合性的系統上，子句 ORDER BY可以分別使用列表 NULLS FIRST或 NULLS LAST，分別在所有資料值之前或之後，對空值進行排序。然而，並非所有資料庫供應商都實現了這一功能。不實現此功能的供應商可能會在 DBMS中，為空值的排序指定不同的處理方法。對索引操作的影響.某些 SQL產品不可以建立包含 NULL 的索引鍵。例如，PostgreSQL 8.3 版本之前 B樹索引的檔案說明，B－樹可以處理資料的等式和範圍查詢，這些資料可以按照某種順序排序。特別是，PostgreSQL查詢規劃器會在使用 等效於這些運算子:在強制唯一性的建立索引執行情況下，欄位為 NULL值會從索引中被排除，並且不會在 NULL之間強制執行唯一性。再次引用 PostgreSQL檔案：當索引被宣告是唯一時，將不允許具有相同索引值的多個錶行。空值不被視為相等。多列唯一索引只會拒絕所有索引列在兩行中相等的情況。這種作法符合 SQL：2003-純量空值比較的規範。索引空值的另一種方法涉及按照 SQL：2003定義的行為將它們處理為不明顯。例如，Microsoft SQL Server文件宣告如下：為了建立索引，NULL比較相等。因此，如果鍵在多行中為 NULL，則無法建立唯一索引或 UNIQUE 約束。當選擇唯一索引或唯一約束的列時，選擇定義為 NOT NULL 的列。這兩種索引策略都與 SQL：2003定義的 Null行為一致。因為索引方法沒有被 SQL：2003標準明確定義，所以空值的索引策略完全由供應商來設計和實現。空值處理函式.SQL定義了兩個函式來顯式處理空值："NULLIF" 和 "COALESCE"。這兩個函式都是 CASE表示式的縮寫。NULLIF.NULLIF 函式接受兩個引數。如果第一個引數等於第二個引數，則 NULLIF 返回 Null。否則，返回第一個引數的值。NULLIF(value1, value2)因此，NULLIF是以下 CASE表示式的縮寫：CASE WHEN value1 = value2THENNULLELSEvalue1ENDCOALESCE.COALESCE 函式實現了空值結合運算子。接受引數列表，從列表中返回第一個非 Null值：COALESCE(value1, value2, value3, ...)COALESCE被定義為以下SQL CASE表示式的簡寫：CASE WHEN value1 IS NOT NULL THEN value1WHEN value2 IS NOT NULL THEN value2WHEN value3 IS NOT NULL THEN value3END一些 SQL DBMS 供應商實作特定的功能類似 COALESCE 函式。某些系統實作為 ISNULL函式，或者功能類似 COALESCE 的其他函式。NVL.ORACLE NVL函式接受兩個引數。它返回第一個非 NULL引數，如果所有引數都是 NULL，則返回 NULL。COALESCE 表示式可被轉換成等效的 NVL表示式這樣的：COALESCE ( val1, ... , val{n} )變成：NVL( val1 , NVL( val2 , NVL( val3 , … , NVL ( val{n-1} , val{n} ) … )))這個函式的用例是在一個表示式中用某一特定值替換 NULL，例如 codice_26，意為'若薪資欄位缺少值，則以 0 替換它'。但有一個明顯例外，在大多數實作中 COALESCE 只評估其引數列表到逹第一個非 NULL值，這有幾個重要的原因：第一個非 NULL 引數之後的引數可能是一個函式，它可能在計算成本上很昂貴、無效、或者可能會產生意料之外的副作用；然而 NVL 將評估引數列表其中的所有引數。資料型別為空和未知.codice_27 字面量在SQL中是無型別的。SQL-92引入了codice_28，允許把codice_27字面量強制轉為特定型別的Null，例如CAST (NULL AS INTEGER)表示INTEGER型別的未知值。