行为诡异的子查询与非常用谓词ANY/ALL/SOME

行为诡异的子查询
假定你现在想从Northwind数据库中查询所有未给客户LAZYK发送订单的发货人，由数据可知，只有发货人1（Speedy Express）是唯一满足条件的。下边查询期望能返回给定结果：
SELECT ShipperID, CompanyName
FROM dbo.Shippers
WHERE ShipperID NOT IN
(SELECT ShipperID FROM dbo.Orders
WHERE CustomerID = N'LAZYK');
令人惊奇的是，它返回空集。你能发现其中的问题吗？
显然，在Orders表中，保存ShipperID的列名为ShipVia而不是ShipperID，在Orders表中根本没有ShipperID这列。需要认识到的是，你大概会期望因为这无效列名而导致查询失败。可以肯定的是，假如你只运行里边的自包含子查询，它肯定会失败："Invalid column name 'ShipperID'."但是，在外部查询里，该子查询又变成有效的了！ 列名称解析是由里层到外层的，查询处理器首先会在里层Orders表中查询ShipperID列，假如找不到这列，它将在外层表Shippers中继续查找。有意无意地，子查询变得具有相关性，假如我们用这种方式来写相应代码：
SELECT ShipperID, CompanyName
FROM dbo.Shippers AS S
WHERE ShipperID NOT IN
(SELECT S.ShipperID FROM dbo.Orders AS O
WHERE O.CustomerID = N'LAZYK');
逻辑上来讲，查询并没有搞清楚自己的意图；然后，他是技术上有效的查询语句。
你现在可以明白为什么返回的是空集了，除非这里没有任何发给LAZYK客户的订单，而很显然，发货人n总会在结果集（select n from dbo.Orders where CustomerID='LAZYK'）里边. 因此NOT IN谓词将永远生成FALSE。最终这个错误的查询将与下述NOT EXISTS类型查询代码逻辑等价：
SELECT ShipperID, CompanyName
FROM dbo.Shippers
WHERE NOT EXISTS
(SELECT * FROM dbo.Orders
WHERE CustomerID = N'LAZYK');
为了解决这个问题，你需要更正列名而使用Orders表中代表ShipperID的ShipVia列来表示
SELECT ShipperID, CompanyName
FROM dbo.Shippers AS S
WHERE ShipperID NOT IN
(SELECT O.ShipVia FROM dbo.Orders AS O
WHERE O.CustomerID = N'LAZYK');
/*
ShipperID CompanyName
----------- ----------------------------------------
1 Speedy Express

(1 行受影响)
*/
一个很实用的方法是，无论你的子查询是否为自包含类型，对子查询的所有特征列引入表名或者表别名，当对应表下列不存在时，就会检查出错：
SELECT ShipperID, CompanyName
FROM dbo.Shippers AS S
WHERE ShipperID NOT IN
(SELECT O.ShipperID FROM dbo.Orders AS O
WHERE O.CustomerID = N'LAZYK');
/*
消息207，级别16，状态1，第4 行
列名'ShipperID' 无效。
*/
正确写法为：
SELECT ShipperID, CompanyName
FROM dbo.Shippers AS S
WHERE ShipperID NOT IN
(SELECT O.ShipVia FROM dbo.Orders AS O
WHERE O.CustomerID = N'LAZYK');

非常用谓词
除IN与EXISTS谓词外，在SQL中还有三个使用很少的谓词：ANY，SOME跟ALL。你可以将它们理解为IN谓词的一般化。（ANY跟SOME在逻辑上等价）
IN谓词被理解为一系列由OR操作符连接而成的逻辑表达式，比如说，v IN(x,y,z) 被理解为v=x OR v=y OR v=z. ANY（或者SOME）允许你标明各个断言的比较关系，而不仅仅是等同关系。比如，v<>ANY(x,y,z)被理解为v<>x OR v<> y OR v<>z.
ALL也类似，被理解为一系列由AND操作符连接而成的逻辑表达式。举个例子v<>ALL(x,y,z)等同于v<>xAND v<>y AND v<>z.
Note: IN后列表可以为返回单列的子查询，或者表达式列表。ANY/SOME以及ALL只支持子查询作为输入集。假如你想用表达式列表，你就需要先把它转换为子查询语句。不是用v <> ANY(x, y, z)这种形式，而是通过v <> ANY(SELECT x UNION ALL SELECT y UNION ALL SELECT z)来实现。
为了演示这种非常用谓词，假设现在我们需要返回每个employee的最小OrderID。下边语句是通过ANY操作运算实现：
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS O1
WHERE
ORderID <=
--NOT OrderID >
ANY(SELECT OrderID
FROM dbo.Orders AS O2
WHERE O2.EmployeeID = O1.EmployeeID);
当然，我们还有下边更普通的写法，这也是为什么这些谓词使用的少的原因：
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS O1
WHERE OrderID =
(SELECT MIN(OrderID)
FROM dbo.Orders AS O2
WHERE O2.EmployeeID = O1.EmployeeID);