fbpx
维基百科

空值 (SQL)

空值(英語:Null、NULL)是結構化查詢語言中使用的特殊標記,是關聯式資料庫中对數據属性未知或缺失的一种标识,用於指示數據庫中不存在數據值。由關聯式資料庫模型的創作者 E.F.科德所引入。SQL空值是用來滿足真實關係數據庫管理系統(RDBMS)中,支持“缺失資訊與不適用的資訊”的需求。科德還介紹了在數據庫理論中使用小寫的希臘字母(ω)符號來表示空值。在 SQL中則是以 NULL 用於標識空值的保留關鍵字。SQL null是一個狀態,而不是一個值。這種用法與大多數編程語言完全不同,其中參照的空值意味著不指向任何對象。

小写希腊字母的omega (ω)字符用来表示数据库理论中的Null。

這不應與 0 數值混淆。空值表示缺少值-而與零值不同,與缺乏答案的方式不同,作為“否”的答案。例如“亞當擁有多少本書?”這個問題,答案可能是“零”(他沒有)或“空白”(不知道他擁有多少)。在數據庫表格中,回報此問題的列結果,將從沒有值(標記為Null)開始,並且在我們確定亞當沒有書籍之前,並不會更新為值“零”。

数据库表主键的取值不能为空值。另外,数据库中的统计计算,一般将有空值的数据忽略不计。

應用 编辑

  • 作為插入資料時,欄位的預設值
  • 查詢時的條件式
在SQL的Where條件式去判斷id欄位是否為Null時,像是 where id = null 是無法正確執行的,必須寫成 where id is null ;反之若要判斷為id欄位是否為非Null時,則用where id is not null
  • 查詢時的空值評估取代(n為評估式或欄位名稱,e為當n為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語義相關的論文(如 SQL中所採用的)是他 1979年在 ACM Transactions on Database Systems上發表的論文,其中他還介紹了他的 Relational Model/Tasmania,儘管其他許多提議後面的文章一直很模糊。1979年他的論文2.3節詳細描述了空值在做算術運算時散播的語義,以及比較空值時使用三值(三元)邏輯的比較;他還詳細說明了對其它集合操作的空值處理(後者目前仍存在爭議)。在數據庫理論界,Codd最初的建議(1975,1979)現在被稱為“Codd tables”。 Codd後來強化了他的要求,即在 ComputerWorld雜誌上發表的一篇 1985年由兩部分組成的文章中,所有 RDBMS都支持 Null來表示缺失的數據。

在 IBM System R中實現原型後,1986年的 SQL標準基本上採用了 Codd的建議。雖然 Don Chamberlin認為nulls(和重複行一起)是 SQL最有爭議的特性之一,但他辯護 SQL中的 Nulls設計,並提出了實用的觀點,認為這是系統對缺少資訊支援最方便的形式,從而使程序員免受許多重複的應用程序級別檢查(請參閱semipredicate問題),同時也為數據庫設計人員提供了不願意使用 Null的選項; 例如,為了避免眾所周知的異常(在本文的語義部分討論)。Chamberlin還認為,除了提供一些缺失值功能之外,對於空值的實踐經驗也會導致依賴於空值的其它語言特性,如某些分組構造和外連接。最後,他認為在實踐中,Nulls最終也會被用作快速修補現有模式的一種方法,當它需要超出其原始意圖的時候,不是為了丟失而是為了不適用的數據編碼; 例如,一個數據庫如果有每英里加侖的欄位,需要快速新增值為電動汽車時。

Codd在其 1990年出版的“數據庫管理關係模型第2版”中指出,SQL標準所要求的單一 Null 類型是不夠的,應該用兩種不同的 Null 類型標記來替代,以表明數據丟失的原因。在 Codd的書中,這兩個 Null型標記分別被稱為“A值”和“I值”,分別表示“缺失但適用”和“缺失但不適用”。 Codd的建議將要求擴展 SQL的邏輯系統以適應四值邏輯系統。由於這種額外的複雜性,具有不同定義類型的多個空值概念,尚未在數據庫領域廣泛接受。儘管如此,它仍然是一個活躍的研究領域,許多論文還在發表。

挑戰 编辑

由於其相關的三值邏輯(3VL),它在 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和空字符串被認為是相同的,因此'Fish'|| NULL || '晶片'字串連接的結果是 '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的實作必須提供專門的三值邏輯(3VL)。SQL三值邏輯的規則如下表所示(   表示邏輯狀態)“ SQL使用 AND,OR和 NOT 的真值表,對應於 Kleene和 Łukasiewicz三值邏輯的常見片段,有價值的邏輯(它們的含義定義不同,但 SQL沒有定義這樣的操作)

p q p OR q p AND q p = q
True True True True True
True False True False False
True 未知 True 未知 未知
False True True False False
False False False False True
False 未知 未知 False 未知
未知 True True 未知 未知
未知 False 未知 False 未知
未知 未知 未知 未知 未知
p NOT p
True False
False True
未知 未知

WHERE子句中的未知效應 编辑

在數據操縱語言(DML)和查詢的比較謂詞中,若遇到 SQL三值邏輯,該 WHERE子句會導致 DML語句僅對那些謂詞評估為 True的列結果起作用;若是 INSERT,UPDATE 或者 DELETE 等 DML語句,則因為此謂詞計算的結果為假、或是未知列,不會執行,並放棄 SELECT查詢。將 Unknown 解釋成 False 相同的邏輯結果,是處理空值時會遇到的常見錯誤,下面的簡單例子說明了這個謬誤:

SELECT * FROM t WHERE i = NULL; 

上例的查詢邏輯一定返回零列,因為 i 欄位與 Null 比較的結果一定是傳回“未知”,即使對於那些 i 為 Null 的資料列也是如此。未知結果導致 SELECT語句立即丟棄每一列。(但在實作中,一些 SQL工具將使用與 Null的比較來檢索列。)

空值指定和3VL特定比較謂詞 编辑

基本 SQL比較運算符在與 Null進行比較時,始終返回“未知”,因此 SQL標準提供了兩個特定的 Null 比較謂詞: IS NULLIS NOT NULL(使用後綴語法),來檢測數據是不是空值。

SQL標準包含一個擴展 F571“真值測試”,它引入了三個額外的邏輯一元運算符(實際上,如果我們計算它們的否定,它們是其語法的一部分),也使用了後綴表示法。他們有以下真值表:

p true false unknown
p IS TRUE true false false
p IS NOT TRUE false true true
p IS FALSE false true false
p IS NOT FALSE true false true
p IS UNKNOWN false false true
p IS NOT UNKNOWN true true false

F571擴展與 SQL中布林數據類型的存在正交(本文稍後討論),儘管語法相似,但 F571不會在語言中引入布林值或三值文字。1999年,在布林型數據類型被引入標準之前,F571擴展實際上存在於 SQL92中。然而,F571擴展只有少數系統實作;PostgreSQL是實作它的數據庫之一。

加入三值邏輯中的其它運算符使 SQL 的三值邏輯功用完善,這意味著它的邏輯運算符可以表示(以組合)任何可能的三值邏輯函數。

在不支持 F571擴展的系統上可以遍歷每種可能組合,使表達式 p Unknown的參數來模擬 IS UNKNOWN p,並使用 IS NULL或其他 NULL特定函數來測試這些參數,雖然這樣子可能更繁瑣。

WHERE子句的排中律 编辑

空值在其它構造式中的影響 编辑

連接(JOIN) 编辑

CASE表達式 编辑

SQL提供了兩種風格的條件表達式。一為“簡單 CASE”並像 switch 語句一樣操作。另一在標準中稱為“搜索 CASE”,並像 if ... elseif一樣運行。

簡單 CASE 表達式使用隱式相等比較,它們的執行和 DML的 WHERE 子句處理 Null 的規則是相同的。因此,一個簡單 CASE 表達式無法直接檢查 Null 的存在。在簡單 CASE 表達式中檢查 Null 總會導致結果為未知,如下所示:

SELECT CASE i WHEN NULL THEN 'Is Null' -- This will never be returned  WHEN 0 THEN 'Is Zero' -- This will be returned when i = 0  WHEN 1 THEN 'Is One' -- This will be returned when i = 1  END FROM t; 

不管 i 列所包含的的值是什麼(即使它包含Null),該表達式的計算結果都為“未知” ,'Is Null' 字串將永遠不會返回。

另一方面,“搜索” CASE表達式可以使用謂詞 IS NULLIS NOT NULL 條件。下例顯示如何使用搜索 CASE表達式來正確檢查 Null:

SELECT CASE WHEN i IS NULL THEN 'Null Result' -- This will be returned when i is NULL  WHEN i = 0 THEN 'Zero' -- This will be returned when i = 0  WHEN i = 1 THEN 'One' -- This will be returned when i = 1  END FROM t; 

Oracle的 SQL 方言提供了一個內置函數 DECODE,可以用它來代替簡單的 CASE表達式,並考慮兩個相等的空值。

SELECT DECODE(i, NULL, 'Null Result', 0, 'Zero', 1, 'One') FROM t; 

如果最後都沒有與條件相匹配的結果,所有這些結構將會返回 NULL;它們有一個預設的 ELSE NULL 子句。

程序擴展中的 IF語句 编辑

SQL/PSM(SQL預存持續模組)定義 SQL的預存程序擴展,例如 IF 語句。但歷史上主要的 SQL供應商都包含了他們自己專有的程序擴展。循環和比較的程序擴展在空值比較規則下運行,類似於 DML 語句和查詢。以下預存程序的片段以 ISO SQL 標準格式演示了在 IF 語句中使用 3VL的 Null 。

IF i = NULL THEN  SELECT 'Result is True' ELSEIF NOT(i = NULL) THEN  SELECT 'Result is False' ELSE  SELECT 'Result is Unknown'; 

該IF語句僅對那些評估為 True 的比較執行操作。對於評估為 False 或未知的 IF 語句,該語句將控制傳遞給 ELSEIF 子句,最後傳遞給 ELSE 子句。上面代碼將因與 Null 的比較始終評估為未知,結果一定會是 'Result is Unknown'

分析SQL空值的語義 编辑

選擇和預測:代表性弱 编辑

如果考慮連接或聯合:甚至不表示弱 编辑

檢查約束和外鍵 编辑

外連接 编辑

SQL 的外連接、左外連接和右外連接,會自動生成空值以作為結果表中缺失值的佔位符。如對於左外連接會產生空值,代替左外連接操作後,右側表中欄位缺少值的列。下例使用兩個表來示範左外連接操作產生的空值佔位符。第一個表(Employee)包含員工編號和姓名,而第二個表(PhoneNumber)包含相關的員工編號和電話號碼,如下。

Employee
ID LastName FirstName
1 Johnson Joe
2 Lewis Larry
3 Thompson Thomas
4 Patterson Patricia
PhoneNumber
ID Number
1 555-2323
3 555-9876

以下示例 SQL查詢對這兩個表執行左外連接。

SELECT e.ID, e.LastName, e.FirstName, pn.Number FROM Employee e LEFT OUTER JOIN PhoneNumber pn ON e.ID = pn.ID; 

此查詢生成的結果集演示 SQL如何使用 Null作為右側(PhoneNumber)表中缺少的值的佔位符,如下所示。

Query result
ID LastName FirstName Number
1 Johnson Joe 555-2323
2 Lewis Larry NULL
3 Thompson Thomas 555-9876
4 Patterson Patricia NULL

聚合函數 编辑

SQL定義了聚合函數以簡化伺服器端的數據聚合計算。除 COUNT(*)函數外,所有集合函數都會執行 Null-elimination 步驟,因此計算的最終結果中不包含 Null。

請注意,消除 Null不等於用零替換 Null。例如下表中 AVG(i)(求 i 欄位的平均值)將給出與 AVG(j)不同的結果:

i j
150 150
200 200
250 250
NULL 0

這裡的 AVG(i) 是 200(150, 200 和 250的平均),而 AVG(j) 則是 150(150, 200, 250 和 0 的平均)。在 SQL 聚合函數中,AVG(z)不等於 SUM(z)/COUNT(*),這是一個眾所周知的副作用。

聚合函數的輸出也可以是空值。這裡是一個例子:

SELECT COUNT(*), MIN(e.Wage), MAX(e.Wage) FROM Employee e WHERE e.LastName LIKE '%Jones%'; 

此查詢將始終輸出一行,計算姓氏中包含“Jones”的員工人數,並給出這些員工的最低和最高工資。但是,如果沒有員工符合給定的標準會發生什麼?計算空集的最小值或最大值是不可能的,所以這些結果必須為NULL,表示沒有答案。這不是未知值,它是表示沒有值的空值。結果是:

COUNT(*) MIN(e.Wage) MAX(e.Wage)
0 NULL NULL

當兩個空值相等時:分組,排序和一些設置操作 编辑

由於 SQL 2003將所有 Null標記定義為彼此不相等,因此需要特殊定義才能在執行某些操作時將 Null分組在一起。SQL將“任何兩個彼此相等的值或任何兩個空值”定義為“没有不同”(not distinct)。這允許 SQL在使用 GROUP BY 子句時對 Null 進行分組和排序。

其他 SQL操作,子句和關鍵字在處理空值時使用“没有不同”(not distinct)。這些包括以下內容:

  • PARTITION BY 排序和開窗功能的 ROW_NUMBER子句
  • UNION,INTERSECT和 EXCEPT操作符,它們將NULL視為用於行比較/消除目的相同
  • DISTINCT SELECT查詢中使用的關鍵字

SQL規範中的 UNION操作符效果會違反空值不相等的原則(它們確實相互確定了空值)。因此某些 SQL操作(如聯合或區別)可能產生不能表示確定信息的結果,這與涉及與 NULL進行顯式比較的操作(例如 WHERE上面討論的子句中的那些操作)不同。在 1979年的 Codd提案中(基本上被SQL92採納),通過論證刪除集合操作中的重複項“在比檢索操作的評估中的等式測試更低的細節層次上發生”這種語義不一致性是合理的。

SQL標準沒有明確定義空值的默認排序順序。相反,在符合性的系統上,子句 ORDER BY可以分別使用列表 NULLS FIRST或 NULLS LAST,分別在所有數據值之前或之後,對空值進行排序。然而,並非所有資料庫供應商都實現了這一功能。不實現此功能的供應商可能會在 DBMS中,為空值的排序指定不同的處理方法。

對索引操作的影響 编辑

某些 SQL產品不可以建立包含 NULL 的索引鍵。例如,PostgreSQL 8.3 版本之前 B樹索引的文件說明,

B-樹可以處理數據的等式和範圍查詢,這些數據可以按照某種順序排序。特別是,PostgreSQL查詢規劃器會在使用< ≤ = ≥ > 這些運算符進行比較時,涉及索引列時考慮使用 B-樹索引:

< ≤ = ≥ > 等效於這些運算符(如 BETWEEN 和 IN)組合的構造也可以用 B-樹索引搜索實現。(但請注意,IS NULL 不是等於而且不可索引。):

在強制唯一性的建立索引執行情況下,欄位為 NULL值會從索引中被排除,並且不會在 NULL之間強制執行唯一性。再次引用 PostgreSQL文件:

當索引被宣告是唯一時,將不允許具有相同索引值的多個表行。空值不被視為相等。多列唯一索引只會拒絕所有索引列在兩行中相等的情況。

這種作法符合 SQL:2003-純量空值比較的規範。

索引空值的另一種方法涉及按照 SQL:2003定義的行為將它們處理為不明顯。例如,Microsoft SQL Server文檔聲明如下:

為了建立索引,NULL比較相等。因此,如果鍵在多行中為 NULL,則無法創建唯一索引或 UNIQUE 約束。當選擇唯一索引或唯一約束的列時,選擇定義為 NOT NULL 的列。

這兩種索引策略都與 SQL:2003定義的 Null行為一致。因為索引方法沒有被 SQL:2003標準明確定義,所以空值的索引策略完全由供應商來設計和實現。

空值處理函數 编辑

SQL定義了兩個函數來顯式處理空值:NULLIFCOALESCE。這兩個函數都是 CASE表達式的縮寫。

NULLIF 编辑

NULLIF 函數接受兩個參數。如果第一個參數等於第二個參數,則 NULLIF 返回 Null。否則,返回第一個參數的值。

NULLIF(value1, value2) 

因此,NULLIF是以下 CASE表達式的縮寫:

CASE WHEN value1 = value2  THEN  NULL  ELSE  value1 END 

COALESCE 编辑

COALESCE 函數实现了空值结合运算符。接受參數列表,從列表中返回第一個非 Null值:

COALESCE(value1, value2, value3, ...) 

COALESCE被定義為以下SQL CASE表達式的簡寫:

CASE WHEN value1 IS NOT NULL THEN value1  WHEN value2 IS NOT NULL THEN value2  WHEN value3 IS NOT NULL THEN value3  ...  END 

一些 SQL DBMS 供應商實作特定的功能類似 COALESCE 函數。某些系統(例如 Transact-SQL)實作為 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,例如 NVL(SALARY, 0),意為'若薪資欄位缺少值,則以 0 替換它'。但有一個明顯例外,在大多數實作中 COALESCE 只評估其參數列表到逹第一個非 NULL值,這有幾個重要的原因:第一個非 NULL 參數之後的參數可能是一個函數,它可能在計算成本上很昂貴、無效、或者可能會產生意料之外的副作用;然而 NVL 將評估參數列表其中的所有參數。

數據類型為空和未知 编辑

NULL 字面量在SQL中是无类型的。[1]

SQL-92引入了CAST,允许把NULL字面量强制转为特定类型的Null,例如

CAST (NULL AS INTEGER) 

表示INTEGER类型的未知值。

BOOLEAN數據類型 编辑

爭論 编辑

常見錯誤 编辑

批評 编辑

閉環世界的假設 编辑

参考文献 编辑

  1. ^ Jim Melton; Alan R. Simon. SQL:1999: Understanding Relational Language Components. Morgan Kaufmann. 2002: 53. ISBN 978-1-55860-456-8. 

延伸讀物 编辑

外部連結 编辑

参见 编辑

空值, 空值, 英語, null, null, 是結構化查詢語言中使用的特殊標記, 是關聯式資料庫中对數據属性未知或缺失的一种标识, 用於指示數據庫中不存在數據值, 由關聯式資料庫模型的創作者, 科德所引入, sql空值是用來滿足真實關係數據庫管理系統, rdbms, 支持, 缺失資訊與不適用的資訊, 的需求, 科德還介紹了在數據庫理論中使用小寫的希臘字母, 符號來表示空值, sql中則是以, null, 用於標識空值的保留關鍵字, null是一個狀態, 而不是一個值, 這種用法與大多數編程語言完全不同, 其中參照. 空值 英語 Null NULL 是結構化查詢語言中使用的特殊標記 是關聯式資料庫中对數據属性未知或缺失的一种标识 用於指示數據庫中不存在數據值 由關聯式資料庫模型的創作者 E F 科德所引入 SQL空值是用來滿足真實關係數據庫管理系統 RDBMS 中 支持 缺失資訊與不適用的資訊 的需求 科德還介紹了在數據庫理論中使用小寫的希臘字母 w 符號來表示空值 在 SQL中則是以 NULL 用於標識空值的保留關鍵字 SQL null是一個狀態 而不是一個值 這種用法與大多數編程語言完全不同 其中參照的空值意味著不指向任何對象 小写希腊字母的omega w 字符用来表示数据库理论中的Null 這不應與 0 數值混淆 空值表示缺少值 而與零值不同 與缺乏答案的方式不同 作為 否 的答案 例如 亞當擁有多少本書 這個問題 答案可能是 零 他沒有 或 空白 不知道他擁有多少 在數據庫表格中 回報此問題的列結果 將從沒有值 標記為Null 開始 並且在我們確定亞當沒有書籍之前 並不會更新為值 零 数据库表主键的取值不能为空值 另外 数据库中的统计计算 一般将有空值的数据忽略不计 目录 1 應用 2 歷史 2 1 挑戰 3 空值的散播 3 1 算術運算 3 2 字串連接 4 NULL和三值邏輯的比較 4 1 WHERE子句中的未知效應 4 2 空值指定和3VL特定比較謂詞 4 3 WHERE子句的排中律 4 4 空值在其它構造式中的影響 4 4 1 連接 JOIN 4 4 2 CASE表達式 4 4 3 程序擴展中的 IF語句 5 分析SQL空值的語義 5 1 選擇和預測 代表性弱 5 2 如果考慮連接或聯合 甚至不表示弱 6 檢查約束和外鍵 7 外連接 8 聚合函數 9 當兩個空值相等時 分組 排序和一些設置操作 10 對索引操作的影響 11 空值處理函數 11 1 NULLIF 11 2 COALESCE 11 3 NVL 12 數據類型為空和未知 13 BOOLEAN數據類型 14 爭論 14 1 常見錯誤 14 2 批評 14 3 閉環世界的假設 15 参考文献 16 延伸讀物 17 外部連結 18 参见應用 编辑作為插入資料時 欄位的預設值 查詢時的條件式在SQL的Where條件式去判斷id欄位是否為Null時 像是 where id null 是無法正確執行的 必須寫成 where id is null 反之若要判斷為id欄位是否為非Null時 則用where id is not null查詢時的空值評估取代 n為評估式或欄位名稱 e為當n為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等資料庫支援 dd 歷史 编辑EF Codd 在一篇 1975年的ACM SIGMOD FDT報告的論文中提到了空值作為表示關係模型中缺失數據的一種方法 Codd最常被引用的與Null語義相關的論文 如 SQL中所採用的 是他 1979年在 ACM Transactions on Database Systems上發表的論文 其中他還介紹了他的 Relational Model Tasmania 儘管其他許多提議後面的文章一直很模糊 1979年他的論文2 3節詳細描述了空值在做算術運算時散播的語義 以及比較空值時使用三值 三元 邏輯的比較 他還詳細說明了對其它集合操作的空值處理 後者目前仍存在爭議 在數據庫理論界 Codd最初的建議 1975 1979 現在被稱為 Codd tables Codd後來強化了他的要求 即在 ComputerWorld雜誌上發表的一篇 1985年由兩部分組成的文章中 所有 RDBMS都支持 Null來表示缺失的數據 在 IBM System R中實現原型後 1986年的 SQL標準基本上採用了 Codd的建議 雖然 Don Chamberlin認為nulls 和重複行一起 是 SQL最有爭議的特性之一 但他辯護 SQL中的 Nulls設計 並提出了實用的觀點 認為這是系統對缺少資訊支援最方便的形式 從而使程序員免受許多重複的應用程序級別檢查 請參閱semipredicate問題 同時也為數據庫設計人員提供了不願意使用 Null的選項 例如 為了避免眾所周知的異常 在本文的語義部分討論 Chamberlin還認為 除了提供一些缺失值功能之外 對於空值的實踐經驗也會導致依賴於空值的其它語言特性 如某些分組構造和外連接 最後 他認為在實踐中 Nulls最終也會被用作快速修補現有模式的一種方法 當它需要超出其原始意圖的時候 不是為了丟失而是為了不適用的數據編碼 例如 一個數據庫如果有每英里加侖的欄位 需要快速新增值為電動汽車時 Codd在其 1990年出版的 數據庫管理關係模型第2版 中指出 SQL標準所要求的單一 Null 類型是不夠的 應該用兩種不同的 Null 類型標記來替代 以表明數據丟失的原因 在 Codd的書中 這兩個 Null型標記分別被稱為 A值 和 I值 分別表示 缺失但適用 和 缺失但不適用 Codd的建議將要求擴展 SQL的邏輯系統以適應四值邏輯系統 由於這種額外的複雜性 具有不同定義類型的多個空值概念 尚未在數據庫領域廣泛接受 儘管如此 它仍然是一個活躍的研究領域 許多論文還在發表 挑戰 编辑 由於其相關的三值邏輯 3VL 它在 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和空字符串被認為是相同的 因此 Fish NULL 晶片 字串連接的結果是 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的實作必須提供專門的三值邏輯 3VL SQL三值邏輯的規則如下表所示 p displaystyle p nbsp 和q displaystyle q nbsp 表示邏輯狀態 SQL使用 AND OR和 NOT 的真值表 對應於 Kleene和 Lukasiewicz三值邏輯的常見片段 有價值的邏輯 它們的含義定義不同 但 SQL沒有定義這樣的操作 p q p OR q p AND q p qTrue True True True TrueTrue False True False FalseTrue 未知 True 未知 未知False True True False FalseFalse False False False TrueFalse 未知 未知 False 未知未知 True True 未知 未知未知 False 未知 False 未知未知 未知 未知 未知 未知p NOT pTrue FalseFalse True未知 未知WHERE子句中的未知效應 编辑 在數據操縱語言 DML 和查詢的比較謂詞中 若遇到 SQL三值邏輯 該 WHERE子句會導致 DML語句僅對那些謂詞評估為 True的列結果起作用 若是 INSERT UPDATE 或者 DELETE 等 DML語句 則因為此謂詞計算的結果為假 或是未知列 不會執行 並放棄 SELECT查詢 將 Unknown 解釋成 False 相同的邏輯結果 是處理空值時會遇到的常見錯誤 下面的簡單例子說明了這個謬誤 SELECT FROM t WHERE i NULL 上例的查詢邏輯一定返回零列 因為 i 欄位與 Null 比較的結果一定是傳回 未知 即使對於那些 i 為 Null 的資料列也是如此 未知結果導致 SELECT語句立即丟棄每一列 但在實作中 一些 SQL工具將使用與 Null的比較來檢索列 空值指定和3VL特定比較謂詞 编辑 基本 SQL比較運算符在與 Null進行比較時 始終返回 未知 因此 SQL標準提供了兩個特定的 Null 比較謂詞 IS NULL和 IS NOT NULL 使用後綴語法 來檢測數據是不是空值 SQL標準包含一個擴展 F571 真值測試 它引入了三個額外的邏輯一元運算符 實際上 如果我們計算它們的否定 它們是其語法的一部分 也使用了後綴表示法 他們有以下真值表 p true false unknownp IS TRUE true false falsep IS NOT TRUE false true truep IS FALSE false true falsep IS NOT FALSE true false truep IS UNKNOWN false false truep IS NOT UNKNOWN true true falseF571擴展與 SQL中布林數據類型的存在正交 本文稍後討論 儘管語法相似 但 F571不會在語言中引入布林值或三值文字 1999年 在布林型數據類型被引入標準之前 F571擴展實際上存在於 SQL92中 然而 F571擴展只有少數系統實作 PostgreSQL是實作它的數據庫之一 加入三值邏輯中的其它運算符使 SQL 的三值邏輯功用完善 這意味著它的邏輯運算符可以表示 以組合 任何可能的三值邏輯函數 在不支持 F571擴展的系統上可以遍歷每種可能組合 使表達式 p Unknown的參數來模擬 IS UNKNOWN p 並使用 IS NULL或其他 NULL特定函數來測試這些參數 雖然這樣子可能更繁瑣 WHERE子句的排中律 编辑 空值在其它構造式中的影響 编辑 連接 JOIN 编辑 CASE表達式 编辑 SQL提供了兩種風格的條件表達式 一為 簡單 CASE 並像 switch 語句一樣操作 另一在標準中稱為 搜索 CASE 並像 if elseif一樣運行 簡單 CASE 表達式使用隱式相等比較 它們的執行和 DML的 WHERE 子句處理 Null 的規則是相同的 因此 一個簡單 CASE 表達式無法直接檢查 Null 的存在 在簡單 CASE 表達式中檢查 Null 總會導致結果為未知 如下所示 SELECT CASE i WHEN NULL THEN Is Null This will never be returned WHEN 0 THEN Is Zero This will be returned when i 0 WHEN 1 THEN Is One This will be returned when i 1 END FROM t 不管 i 列所包含的的值是什麼 即使它包含Null 該表達式的計算結果都為 未知 Is Null 字串將永遠不會返回 另一方面 搜索 CASE表達式可以使用謂詞 IS NULL 和 IS NOT NULL 條件 下例顯示如何使用搜索 CASE表達式來正確檢查 Null SELECT CASE WHEN i IS NULL THEN Null Result This will be returned when i is NULL WHEN i 0 THEN Zero This will be returned when i 0 WHEN i 1 THEN One This will be returned when i 1 END FROM t Oracle的 SQL 方言提供了一個內置函數 DECODE 可以用它來代替簡單的 CASE表達式 並考慮兩個相等的空值 SELECT DECODE i NULL Null Result 0 Zero 1 One FROM t 如果最後都沒有與條件相匹配的結果 所有這些結構將會返回 NULL 它們有一個預設的 ELSE NULL 子句 程序擴展中的 IF語句 编辑 SQL PSM SQL預存持續模組 定義 SQL的預存程序擴展 例如 IF 語句 但歷史上主要的 SQL供應商都包含了他們自己專有的程序擴展 循環和比較的程序擴展在空值比較規則下運行 類似於 DML 語句和查詢 以下預存程序的片段以 ISO SQL 標準格式演示了在 IF 語句中使用 3VL的 Null IF i NULL THEN SELECT Result is True ELSEIF NOT i NULL THEN SELECT Result is False ELSE SELECT Result is Unknown 該IF語句僅對那些評估為 True 的比較執行操作 對於評估為 False 或未知的 IF 語句 該語句將控制傳遞給 ELSEIF 子句 最後傳遞給 ELSE 子句 上面代碼將因與 Null 的比較始終評估為未知 結果一定會是 Result is Unknown 分析SQL空值的語義 编辑選擇和預測 代表性弱 编辑 如果考慮連接或聯合 甚至不表示弱 编辑檢查約束和外鍵 编辑外連接 编辑SQL 的外連接 左外連接和右外連接 會自動生成空值以作為結果表中缺失值的佔位符 如對於左外連接會產生空值 代替左外連接操作後 右側表中欄位缺少值的列 下例使用兩個表來示範左外連接操作產生的空值佔位符 第一個表 Employee 包含員工編號和姓名 而第二個表 PhoneNumber 包含相關的員工編號和電話號碼 如下 Employee ID LastName FirstName1 Johnson Joe2 Lewis Larry3 Thompson Thomas4 Patterson Patricia PhoneNumber ID Number1 555 23233 555 9876以下示例 SQL查詢對這兩個表執行左外連接 SELECT e ID e LastName e FirstName pn Number FROM Employee e LEFT OUTER JOIN PhoneNumber pn ON e ID pn ID 此查詢生成的結果集演示 SQL如何使用 Null作為右側 PhoneNumber 表中缺少的值的佔位符 如下所示 Query result ID LastName FirstName Number1 Johnson Joe 555 23232 Lewis Larry NULL3 Thompson Thomas 555 98764 Patterson Patricia NULL聚合函數 编辑SQL定義了聚合函數以簡化伺服器端的數據聚合計算 除 COUNT 函數外 所有集合函數都會執行 Null elimination 步驟 因此計算的最終結果中不包含 Null 請注意 消除 Null不等於用零替換 Null 例如下表中 AVG i 求 i 欄位的平均值 將給出與 AVG j 不同的結果 i j150 150200 200250 250NULL 0這裡的 AVG i 是 200 150 200 和 250的平均 而 AVG j 則是 150 150 200 250 和 0 的平均 在 SQL 聚合函數中 AVG z 不等於 SUM z COUNT 這是一個眾所周知的副作用 聚合函數的輸出也可以是空值 這裡是一個例子 SELECT COUNT MIN e Wage MAX e Wage FROM Employee e WHERE e LastName LIKE Jones 此查詢將始終輸出一行 計算姓氏中包含 Jones 的員工人數 並給出這些員工的最低和最高工資 但是 如果沒有員工符合給定的標準會發生什麼 計算空集的最小值或最大值是不可能的 所以這些結果必須為NULL 表示沒有答案 這不是未知值 它是表示沒有值的空值 結果是 COUNT MIN e Wage MAX e Wage 0 NULL NULL當兩個空值相等時 分組 排序和一些設置操作 编辑由於 SQL 2003將所有 Null標記定義為彼此不相等 因此需要特殊定義才能在執行某些操作時將 Null分組在一起 SQL將 任何兩個彼此相等的值或任何兩個空值 定義為 没有不同 not distinct 這允許 SQL在使用 GROUP BY 子句時對 Null 進行分組和排序 其他 SQL操作 子句和關鍵字在處理空值時使用 没有不同 not distinct 這些包括以下內容 PARTITION BY 排序和開窗功能的 ROW NUMBER子句 UNION INTERSECT和 EXCEPT操作符 它們將NULL視為用於行比較 消除目的相同 DISTINCT SELECT查詢中使用的關鍵字SQL規範中的 UNION操作符效果會違反空值不相等的原則 它們確實相互確定了空值 因此某些 SQL操作 如聯合或區別 可能產生不能表示確定信息的結果 這與涉及與 NULL進行顯式比較的操作 例如 WHERE上面討論的子句中的那些操作 不同 在 1979年的 Codd提案中 基本上被SQL92採納 通過論證刪除集合操作中的重複項 在比檢索操作的評估中的等式測試更低的細節層次上發生 這種語義不一致性是合理的 SQL標準沒有明確定義空值的默認排序順序 相反 在符合性的系統上 子句 ORDER BY可以分別使用列表 NULLS FIRST或 NULLS LAST 分別在所有數據值之前或之後 對空值進行排序 然而 並非所有資料庫供應商都實現了這一功能 不實現此功能的供應商可能會在 DBMS中 為空值的排序指定不同的處理方法 對索引操作的影響 编辑某些 SQL產品不可以建立包含 NULL 的索引鍵 例如 PostgreSQL 8 3 版本之前 B樹索引的文件說明 B 樹可以處理數據的等式和範圍查詢 這些數據可以按照某種順序排序 特別是 PostgreSQL查詢規劃器會在使用 lt gt 這些運算符進行比較時 涉及索引列時考慮使用 B 樹索引 lt gt 等效於這些運算符 如 BETWEEN 和 IN 組合的構造也可以用 B 樹索引搜索實現 但請注意 IS NULL 不是等於而且不可索引 在強制唯一性的建立索引執行情況下 欄位為 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 value2 THEN NULL ELSE value1 END COALESCE 编辑 COALESCE 函數实现了空值结合运算符 接受參數列表 從列表中返回第一個非 Null值 COALESCE value1 value2 value3 COALESCE被定義為以下SQL CASE表達式的簡寫 CASE WHEN value1 IS NOT NULL THEN value1 WHEN value2 IS NOT NULL THEN value2 WHEN value3 IS NOT NULL THEN value3 END 一些 SQL DBMS 供應商實作特定的功能類似 COALESCE 函數 某些系統 例如 Transact SQL 實作為 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 例如 NVL SALARY 0 意為 若薪資欄位缺少值 則以 0 替換它 但有一個明顯例外 在大多數實作中 COALESCE 只評估其參數列表到逹第一個非 NULL值 這有幾個重要的原因 第一個非 NULL 參數之後的參數可能是一個函數 它可能在計算成本上很昂貴 無效 或者可能會產生意料之外的副作用 然而 NVL 將評估參數列表其中的所有參數 數據類型為空和未知 编辑参见 Option type和Nullable type NULL 字面量在SQL中是无类型的 1 SQL 92引入了CAST 允许把NULL字面量强制转为特定类型的Null 例如 CAST NULL AS INTEGER 表示INTEGER类型的未知值 BOOLEAN數據類型 编辑爭論 编辑常見錯誤 编辑 批評 编辑 閉環世界的假設 编辑参考文献 编辑 Jim Melton Alan R Simon SQL 1999 Understanding Relational Language Components Morgan Kaufmann 2002 53 ISBN 978 1 55860 456 8 延伸讀物 编辑外部連結 编辑参见 编辑SQL 取自 https zh wikipedia org w index php title 空值 SQL amp oldid 75526161, 维基百科,wiki,书籍,书籍,图书馆,

文章

,阅读,下载,免费,免费下载,mp3,视频,mp4,3gp, jpg,jpeg,gif,png,图片,音乐,歌曲,电影,书籍,游戏,游戏。