在 SQL Server 数据库的 WHERE 语句中使用子查询

时　间:2016-11-11 08:32:39
作　者:缪炜   ID：24010  城市：江阴
摘　要:这是关于子查询语句的一系列文章中的第三篇。在这篇文章中我们将讨论WHERE语句中的子查询语句。其他的文章讨论了其他语句中的子查询语句。
本次课程中的所有例子都是基于Microsoft SQL Server Management Studio和AdventureWorks2012数据库的。读者可以阅读我的SQL Server使用入门学习使用这些免费的工具。
在WHERE语句中使用子查询
正　文:

在Where语句中使用子查询

在Where语句中使用子查询是非常常见的。常见的用法是用EXISTS或IN测试存在性。在某些情况下重新考虑查询语句并且使用JOIN是有意义的，但是在最终决定之前，应该好好学习一下这两种通过查询优化的形式。

比较修饰符ANY和ALL可以和greater than，less than，或者equals操作符一起使用。这样就提供了一种方式比较一个单值，例如一个列，一个或更多的子查询返回的结果。

现在我们详细讨论这些。

Exist和Not Exists

EXISTS条件结合子查询使用.当子查询返回一个或多个记录的时候，它返回TRUE.

EXISTS条件最简单的语法形式如下：

假设我们需要返回，所有由销售人员记录的，从年初至今，大于三百万美元的销售订单.为了达到这个效果，我们可以使用如下例子中的EXISTS子句:  

Select SalesOrderID,
       RevisionNumber,
       OrderDate
FROM   Sales.SalesOrderHeader
Where  EXISTS (Select 1
               FROM   sales.SalesPerson
               Where  SalesYTD > 3000000
                      AND SalesOrderHeader.SalesPersonID 
                        = Sales.SalesPerson.BusinessEntityID)

 

执行这条SQL语句的时候，会作如下的对比:

1. Where子句返回所有EXISTS子句为TRUE的记录.

2. EXIST子句使用相关子查询. 外部查询通过SalesPersonID关联到内部查询.

3. 只有SalesYTD大于三百万美元的SalesPersons才会包含在结果之中.

4. 如果子查询返回一行或多行记录，EXISTS子句返回TRUE.

EXISTS条件是一种成员条件，即只有返回结果它才返回TRUE.相反的，如果我们要测试非成员条件，我们可以使用NOT EXISTS.

当无记录返回时，NOT EXISTS返回TRUE.这样，如果我们想找出，所有由销售人员记录的，从年初至今，小于等于三百万美元的销售订单，我们可以使用如下的查询:

Select SalesOrderID,
       RevisionNumber,
       OrderDate
FROM   Sales.SalesOrderHeader
Where  NOT EXISTS (Select 1
               FROM   sales.SalesPerson
               Where  SalesYTD > 3000000
                      AND SalesOrderHeader.SalesPersonID 
                        = Sales.SalesPerson.BusinessEntityID)

NULL是怎么回事?

当一个子查询返回null值的时候，EXISTS会返回什么:  NULL， TRUE， 还是 FALSE？

坦白讲，我也很吃惊.

我很确信它会返回NULL， 但让人吃惊的是，我获知它返回TRUE。 因此，如果你的子查询返回null值，EXISTS子句会返回TRUE。 下面的例子会返回所有的SalesOrderHeader行，因为Where子句返回TRUE:

Select SalesOrderID,
       RevisionNumber,
       OrderDate
FROM   Sales.SalesOrderHeader
Where  EXISTS (Select NULL)

当我们学习IN操作符的时候，我们会发现，这是EXISTS子句所独有的特性.

IN 和 NOT IN

我们首先在如何过滤查询结果的课程中学习IN操作符。在子查询中使用的时候，IN 和 NOT IN 子句是一样的。下面是那篇文章的小结。

IN 和 NOT IN 回顾

IN操作符被看作是一个成员类型。成员类型允许在一条语句中进行多次匹配测试。例如，假如有公司领导的多个拼写形式，如“Owner”，“President”，和“CEO”。在这种情况下，你可以使用IN操作符来找到所有的匹配形式：

ContactTitle IN ('CEO', 'Owner', 'President')

如果ContactTitle是“CEO”，“Owner"或者“President"，上面的语句将匹配或者返回TRUE。为了使用IN操作符，需要使用逗号分隔测试项，并括在括号中。例子中完整的语句是：

Select CompanyName, ContactName, ContactTitle
FROM Customers
Where ContactTitle IN ('CEO', 'Owner', 'President');
 
Note:  The above query isn't meant for 
the adventure works database

在子查询中使用IN

当在子查询中使用的时候，数据列表可以用子查询来代替。在这种情况下，使用子查询的优势是，它有助于让查询处于数据驱动的状态，同时更易于处理。

我的意思是，你无需硬编码数据。

例如，如果你正在做一个查询，需要查找销售最多的销售人员的订单，非子查询的方式使用IN的语句是：

Select SalesOrderID,
       OrderDate,
       AccountNumber,
       CustomerID,
       SalesPersonID,
       TotalDue
  FROM Sales.SalesOrderHeader
  Where SalesPersonID IN (279, 286, 289)
 
whose results are

 

但是，现在我们知道了子查询，我们可以使用下面的语句获取同样的数据：

Select SalesOrderID,
       OrderDate,
       AccountNumber,
       CustomerID,
       SalesPersonID,
       TotalDue
  FROM Sales.SalesOrderHeader
  Where SalesPersonID IN (Select BusinessEntityID
                            FROM Sales.SalesPerson
                           Where Bonus > 5000)

其优势在于，随着销售人员销售的数据变化，返回的SalesPersonID的数据也跟着调整。

和其它的查询一样，你可以使用IN子句来创建一个关联子查询。这和EXIST子句使用的是相同的查询。

它返回了，从年初至今，所有销售人员记录的大于三百万美元的销售订单，但是现在我们使用IN子句：

Select SalesOrderID,
       RevisionNumber,
       OrderDate
FROM   Sales.SalesOrderHeader
Where  SalesPersonID IN (Select SalesPerson.BusinessEntityID
               FROM   sales.SalesPerson
               Where  SalesYTD > 3000000
                      AND SalesOrderHeader.SalesPersonID 
                        = Sales.SalesPerson.BusinessEntityID)

 

如果在比较列表中测试值 找到了 ，IN返回TRUE。如果在比较列表中测试值 没有找到 ，NOT IN返回TRUE。采用和上面一样的查询，我们可以找到，从年初至今，所有销售人员记录的小于等三百万美元的销售订单，查询语句如下：

Select SalesOrderID,
       RevisionNumber,
       OrderDate
FROM   Sales.SalesOrderHeader
Where  SalesPersonID NOT IN (Select SalesPerson.BusinessEntityID
               FROM   sales.SalesPerson
               Where  SalesYTD > 3000000
                      AND SalesOrderHeader.SalesPersonID 
                        = Sales.SalesPerson.BusinessEntityID)

在IN中碰到NULL是怎么回事?

当比较列表值包含NULL值的时候，和此列表比较的任何数据均返回false。

例如：

Select SalesOrderID,
       RevisionNumber,
       OrderDate
FROM   Sales.SalesOrderHeader
Where  SalesOrderID  IN (Select NULL)

 子查询首先计算平均安全库存等级。它返回一个数值列表。然后对每一个产品行的安全库存等级对外查询比较。如果它大于一个或多个列表中的值，就会被包含在结果中返回

 如我一样，你可能第一反应是>ANY是多余的，鸡肋。它就等同于>MIN(...)，不是吗？

这是可以用ANY，却不能用MIN的示例语句：

Select ProductID,
       Name,
       SafetyStockLevel,
       DaysToManufacture
FROM   Production.Product
Where  SafetyStockLevel > MIN((Select   AVG(SafetyStockLevel)
                               FROM     Production.Product
                               GROUP BY DaysToManufacture)

并不能运行，而是返回一个错误, “Cannot perform an aggregate function on an expression containing an aggregate or a subquery.”　[译者注：不能运行的原因是GROUP BY语句对聚合函数不能用常规关键字操作，而是用另一些代替，例如GROUP BY条件中遇到聚合函数时Where用HAVING替换。]

使用 > ALL 修饰符

 > ALL 修饰符是返回所有比内查询中结果都大的结果。

比较操作>ALL意味着要大于列表中最大的值。示例如下：

Sales > ALL (1000, 2000, 2500)

等同于：

Sales > MAX(1000, 2000, 2500)

对条件 Sales > 2500返回为TRUE

这个例子我们返回年初至今 奖金大于 所有销售收入小于$1000000人员的奖金 的销售人员名单：

Select p.BusinessEntityID,
       p.FirstName,
       p.LastName,
       s.Bonus,
       s.SalesYTD
FROM   Person.Person AS p
       INNER JOIN Sales.SalesPerson AS s
       ON p.BusinessEntityID = s.BusinessEntityID
Where  s.Bonus > ALL (Select Bonus
                      FROM   Sales.SalesPerson
                      Where  Sales.SalesPerson.SalesYTD 
                             < 1000000)

各种比较修饰符小结

你能使用各种比较修饰符进行其他的操作，比如判断相等。如下表格内的示例可以更加便于理解。有些组合可能没有意义，我还是全部列了出来。

示例中的子查询结果集仅包含了三个数值:  1,2,3.

某些比较的组合根本没用。比如“= ALL” 或 “<> ANY.”。工具在于人使用，如果你觉得MAX或MIN可以用就该用，ANY和ALL只出现在它该出现的地方。

我们今天讨论了我在子查询中使用EXISTS和NOT EXISTS的情况。我用了相当多的IN子句，但通常是在一个静态列表，而不是子查询中。

 

Access软件网QQ交流群 (群号:483923997) 　　　　　　Access源码网店