鋒利的SQL2014：使用NULL比較搜尋條件

 摘自作者新書《鋒利的SQL》（第2版），網購京東：http://item.jd.com/11692900.html

SQL使用NULL表示缺失值，NULL值與0、0長度的字元串或空白（字元值）的含義不同。NULL值計算使用三值邏輯，即謂詞可以計算為TRUE、FALSE或UNKNOWN。當邏輯表達式僅涉及已有或是現值時，其計算結果為TRUE或FALSE，但當邏輯表達式涉及缺失值時，其計算結果為UNKNOWN。例如，謂詞salary>0，當salary等于1,000時，表達式計算結果為TRUE；當salary等于–1,000時，表達式計算結果為FALSE；當salary為NULL時，表達式計算結果為UNKNOWN。

對于計算結果為TRUE和FALSE值時，SQL的處理方式很容易了解。例如，如果謂詞salary>0出現在WHERE子句中，将傳回表達式計算為TRUE的行，而那些表達式計算為FALSE的會被篩選掉。同樣，如果謂詞salary>0出現在表的CHECK限制中，所有行的表達式計算為TRUE的INSERT或UPDATE語句會被接受，而那些任何行計算為FALSE的語句會被拒絕。

但是，對于UNKNOWN的處理，SQL不同的語言元素有着不同的處理方式。對于查詢篩選而言，SQL的正确處理定義是“接受TRUE”，意味着FALSE和UNKNOWN會被篩選掉。對于CHECK限制而言，SQL的正确處理定義是“拒絕FALSE”，意味着TRUE和UNKNOWN會被接受。如果SQL使用二值謂詞邏輯，就不會有“接受TRUE”和“拒絕FALSE”的差別。但是對于三值謂詞邏輯，“接受TRUE”是拒絕UNKNOWN（它僅接受TRUE，是以拒絕FALSE和UNKNOWN）。對于前面示例中的謂詞salary>0 ，NULL值的工資會導緻表達式計算結果為UNKNOWN。如果謂詞出現在查詢的WHERE子句中，具有NULL工資的行會被篩選掉；如果謂詞出現在表的CHECK限制中，NULL工資的行會被接受。

UNKNOWN另一個棘手的問題是，使用NOT求反時，它仍然得到UNKNOWN值。例如，給出的謂語NOT (salary>0)，當工資為NULL時，salary>0的計算結果為UNKNOWN，并且NOT UNKNOWN仍就是UNKNOWN。

此外，比較兩個NULL值時（NULL=NULL）計算為UNKNOWN，SQL觀點是：NULL表示缺失或未知值，你真不能說一個未知的值是否等于另一個值。在SQL Server中，提供了一個ANSI_NULLS選項，當SET ANSI_NULLS為ON時，将執行标準SQL行為——兩個NULL值比較時傳回UNKNOWN。當ANSI_NULLS設定為OFF時，兩個NULL值比較傳回TRUE，将一個已知值與空值比較則傳回FLASH。可以使用SESSIONPROPERTY函數檢測ANSI_NULLS選項的設定，傳回1表示ON，0表示OFF。

SELECT SESSIONPROPERTY('ANSI_NULLS');

為了確定語句能夠按預期運作，應當使用謂詞IS NULL和IS NOT NULL替代= NULL和<> NULL，這樣不會受ANSI_NULLS選項的影響。

為了更形象地說明此問題，下面的語句建立了一個dbo.T1表，并插入4行資料，其中第3行的City值為NULL。

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL

  DROP TABLEdbo.T1;

CREATE TABLE dbo.T1

(

  CustIDINT,

  CityNVARCHAR(20)

 );

INSERT INTO dbo.T1 VALUES

(1, N'Beijing'),

(2, N'Shanghai'),

(3, NULL),

(4, N'Beijing');

下面的兩行語句分别用于傳回City為Beijing和不是Beijing的行，查詢結果分别如表5-12和5-13所示。

SELECT * FROM dbo.T1

WHERE City = N'Beijing';  --語句1：傳回City為Beijing的客戶

SELECT * FROM dbo.T1

WHERE City <> N'Beijing'; --語句2：傳回City不是Beijing的客戶

表5-12                                                                 語句1的查詢結果

CustID	City
1	Beijing
4	Beijing

表5-13                                                                 語句2的查詢結果

CustID	City
2	Shanghai

從表5-12可以看出，語句1接受City = N'Beijing'計算結果為TRUE的行，但是會拒絕計算結果為FALSE和UNKNOWN的行，是以CustID為2和3的行被過濾掉了。語句2也是同樣道理，由于CustID為3的行的計算結果為UNKNOWN，也會被過濾掉。即使将語句1的謂詞修改為NOT(City= N'Beijing')，傳回結果也會與表5-13相同，因為對UNKNOWN求反還是UNKNOWN。

要想使語句2得到期望的結果，即傳回City不是Beijing的行，以及City缺失值的行，應當使用下面的語句。查詢結果如表5-14所示。

SELECT * FROM dbo.T1

WHERE City <> N'Beijing'

  OR City ISNULL; --傳回City不是Beijing的客戶

表5-14                                                           修改後語句2的查詢結果

CustID	City
2	Shanghai
3	NULL

在比較和排序時，SQL處理NULL标記的方式也不一緻。例如，對于分組和排序目的，兩個NULL标記被視為相等，即GROUP BY子句将所有NULL标記排列為一組，就像排列現值一樣，并且ORDER BY子句也将所有NULL标記排序在一起。

在索引方面，為了強制執行唯一索引限制，标準SQL将NULL标記視為彼此不同（允許多個NULL标記）。而在SQL Server中，唯一索引限制認為兩個NULL标記是相等的，如果限制定義的是單個列，則該列中隻允許有一個NULL值。

綜上所述，當查詢語句涉及到NULL值比較時，一定要高度注意查詢結果是否達到了預期效果。