T-SQL基础：不当子查询处理

正在翻译MVP写的基础知识读本，非常不错，大家先尝尝鲜。 

本节将介绍子查询行为可能违背你期望的情况，并提供了最佳做法，这样就可以在代码中避免与此类情况相关的逻辑错误。

1．NULL故障

回顾一下，T-SQL使用三值逻辑。本节中，我将演示当子查询涉及NULL标记并且没有考虑三值逻辑情况下演变而来的问题。

请考虑以下看起来比较直观的查询，用于返回没有订单的客户。

SELECT custid, companyname

FROM Sales.Customers

WHERE custid NOT IN(SELECT O.custid

                   FROM Sales.Orders AS O);

当前示例数据在TSQL2012数据库的Orders表中，查询似乎是按照你期望的方式工作。事实上，它返回两个没有订单的客户。

custid companyname
----------- ----------------
22 Customer DTDMN
57 Customer WVAXS

接下来，运行以下代码，插入一个客户ID为NULL的新订单到Orders表中。

INSERT INTO Sales.Orders

  (custid, empid, orderdate,requireddate, shippeddate, shipperid,

   freight,shipname, shipaddress, shipcity, shipregion,

  shippostalcode, shipcountry)

  VALUES(NULL,1, '20090212', '20090212',

        '20090212', 1, 123.00, N'abc', N'abc', N'abc',

        N'abc', N'abc', N'abc');

运行此查询，应当返回没有订单的客户。

SELECT custid, companyname

FROM Sales.Customers

WHERE custid NOT IN(SELECT O.custid

                    FROM Sales.Orders AS O);

这一次，查询返回一个空集合。牢记在第2章“单表查询”中你已经读过的有关NULL标记的部分，试着解释一下为什么该查询返回一个空集合。还要考虑如何让客户22和57输出，一般来说，要找出可以避免此类问题的最佳做法，假设就是这个问题。

显然，这个故事中的罪魁祸首是添加到Orders表中的NULL客户ID，并且现在与已知客户ID被子查询返回了。

让我们从以你期望的行为方式部分开始。对于有订单的客户（例如，客户85）IN谓词返回TRUE，因为这样的客户可以被子查询返回。NOT运算符用于否定IN谓词。因此，NOT TRUE变成了FALSE，并且客户不会被外部查询返回。这意味着当客户ID出现在Orders表中时，可以肯定地说客户有订单，因此你不想让它出现在输出中。然而，当Orders表中有NULL客户ID时，你就不能肯定地说某个确定的客户ID是否出现在Orders中，稍后将解释原因。

对于像22这样没有出现在Orders已知客户ID集合中的客户，IN谓词返回UNKNOWN（真值UNKNOWN类似于真值TRUE和FALSE）。IN谓词对于这样的客户返回UNKNOWN，是因为将其与所有已知客户ID比较生成FALSE，将其与集合中的NULL比较生成UNKNOWN，FALSE OR UNKNOWN生成UNKNOWN。一个更具体的例子，考虑一下表达式22 NOT IN (1, 2, NULL)。此表达式可以换个说法NOT 22 IN (1, 2,NULL)，展开这个表达式为NOT (22 = 1 OR 22 = 2 OR 22= NULL)。计算括号内每个单独的表达式到其真值，你会得到NOT (FALSE OR FALSE ORUNKNOWN)，这会解释为NOT UNKNOWN，计算结果为UNKNOWN。

在应用NOT运算符之前，这里UNKNOWN的逻辑含义是无法确定客户ID是否出现在集合中，因为NULL可以表示客户ID以及其他任何值。有趣的是，使用NOT运算符否定UNKNOWN仍旧生成UNKNOWN，并且查询筛选中的UNKNOWN是被过滤掉的。这意味着，这是一种不知道客户ID是否出现在集合中，也不知道其是否未出现在集合中的情况。

总之，当对一个至少返回一个NULL的子查询使用谓词时，外部查询总是返回空集合。因为外部查询要求返回没有出现在集合中的值，已知出现在集合中的外部表值不会返回。因为在包含NULL的集合中，你永远不能肯定地说值没出现在集合中，没有出现在已知值集合中的值不会返回。

那么，遵循什么方法可以避免这种故障呢？

第一，列不应该允许NULL标记，将其定义为NOT NULL是十分重要的。强制数据完整性比许多人意识到的更加重要的。

第二，你写的所有查询中，应考虑三值逻辑（TRUE、FALSE和UNKNOWN）的所有三种可能的真值。想清楚查询是否会处理NULL标记，如果是这样，NULL标记的默认处理是否适合你的需要。当不适合时，就需要进行干预。例如，在我们分析过的示例中，因为与NULL比较，外部查询返回一个空集合。如果要检查某个客户ID是否出现在已知值集合中，并且忽略了NULL标记，你应该显式或隐式地排除NULL标记。显式排除NULL标记的一个方法是像下面这样添加O.custid IS NOT NULL到子查询中。

SELECT custid, companyname

FROM Sales.Customers 

WHERE custid NOT IN(SELECT O.custid 

                   FROM Sales.Orders AS O

                   WHERE O.custid IS NOT NULL);

你还可以像下面这样使用NOT EXISTS谓词替换NOT IN，隐式地排除NULL标记。

SELECT custid, companyname

FROM Sales.Customers AS C

WHERE NOT EXISTS

  (SELECT* 

   FROMSales.Orders AS O

   WHEREO.custid = C.custid);

回顾一下，与IN不同的是，EXISTS使用两值谓词逻辑。EXISTS总是返回TRUE或FALSE，并且绝不返回UNKNOWN。当子查询受到O.custid中的NULL困扰时，表达式的计算结果为UNKNOWN，并且行被过滤掉。对EXISTS谓词来说，NULL情况被自然过滤掉了，即使不存在NULL。所以EXISTS最终仅处理已知的客户ID，因此，使用NOT EXISTS比NOT IN安全。

完成实验后，请运行以下清理代码。

DELETE FROM Sales.OrdersWHERE custid IS NULL;

2．子查询列名称中的替换错误

代码中的逻辑bug有时非常难以捉摸。本节中，我会描述一个难以捉摸的bug，即子查询列名称中一个幼稚的替换错误。在解释bug之后，我会提供最佳做法，有助于避免未来出现这样的错误。

本节中的示例查询Sales架构中的一个称为MyShippers的表。运行下面的代码创建并填充该表。

IF OBJECT_ID('Sales.MyShippers', 'U') IS NOT NULL

  DROP TABLESales.MyShippers;

 

CREATE TABLE Sales.MyShippers

(

 shipper_id  INT          NOT NULL,

  companynameNVARCHAR(40) NOT NULL,

  phone       NVARCHAR(24) NOT NULL,

  CONSTRAINTPK_MyShippers PRIMARY KEY(shipper_id)

);

 

INSERT INTO Sales.MyShippers(shipper_id,companyname, phone)

  VALUES(1,N'Shipper GVSUA', N'(503) 555-0137'),

        (2,N'Shipper ETYNR', N'(425) 555-0136'),

        (3,N'Shipper ZHISN', N'(415) 555-0138');

请考虑以下查询，应当返回发货订单给客户43的发货人。

SELECT shipper_id, companyname

FROM Sales.MyShippers

WHERE shipper_id IN

  (SELECTshipper_id

   FROMSales.Orders

   WHEREcustid = 43);

此查询生成下列输出。.

shipper_id companyname
----------- ---------------
1 Shipper GVSUA
2 Shipper ETYNR
3 Shipper ZHISN

显然，只有发货人2和3发货给客户43，但由于某些原因，此查询从MyShippers表返回了所有发货人。仔细检查查询以及表涉及的架构，看看你能这是解释为什么吗。

原来，Orders表中的存储发货人ID的列名称不是shipper_id，而是shipperid（无下划线）。MyShippers表中的列叫做shipper_id，具有下划线。子查询上下文中的无前缀列名称可以从当前/内部范围之外解析。在我们的示例中，SQL Server首先在Orders表中查找shipper_id列，如果没有找到，SQL Server在查询的外部表MyShipper中查找。因为找到了shipper_id，所以查询中就使用该列。

你可以看到本应当是一个自包含子查询，无意中变成了一个相关子查询。只要订单表至少有一行，当将外部发货人与查询比较时，会对Orders表中的每行返回很多相同的外部发货人ID，这样MyShippers表中的所有行都会找到一个匹配行。

有些人可能认为，此行为是标准SQL的设计缺陷。不过，ANSISQL委员会中此行为的设计师不认为这是难以察觉的“错误”，相反，引用外部表中的列名而无需为其前缀表名，这是一种有意义的行为，只要这些列名称是明确的（即只要它们仅出现在一个表中）。

在跨表之间未使用一致的属性名称，这种问题是比较常见的。有时名称只是略有不同，如在这个问题中，一个表中是shipperid，另一个是shipper_id。这就足以表明这是一个bug。

要避免此类问题可以遵循两种最佳做法，一个需要从长远计议，另一个在短期内就可以实现。

从长远来看，你的组织应当作为一种策略，不要低估在跨表间使用一致属性名称的重要性。在短期内，你当然不想改变现有的列名称，因为这有可能破坏应用程序代码。

短期内，你可以采用一种非常简单的做法——以源表的别名作为子查询中列名称的前缀。这样，解析过程仅查找指定表中的列，如果没有这样的列，会收到一个解析错误。例如，尝试运行以下代码。

SELECT shipper_id,companyname

FROM Sales.MyShippers

WHERE shipper_id IN

  (SELECT O.shipper_id

   FROMSales.Orders AS O

   WHERE O.custid = 43);

将出现下面的解析错误。

消息 207，级别 16，状态 1，第 4 行

列名 'shipper_id' 无效。

在得到此错误之后，你当然可以识别问题并修正查询。

SELECT shipper_id,companyname

FROM Sales.MyShippers

WHERE shipper_id IN

  (SELECT O.shipperid

   FROMSales.Orders AS O

   WHERE O.custid = 43);

这一次，查询返回了预期的结果。

shipper_id companyname
----------- ---------------
2 Shipper ETYNR
3 Shipper ZHISN

完成后，请运行以下清理代码。

IFOBJECT_ID('Sales.MyShippers', 'U') IS NOT NULL

  DROP TABLE Sales.MyShippers;