锋利的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值比较时，一定要高度注意查询结果是否达到了预期效果。