Access开发培训
网站公告
·Access专家课堂QQ群号:151711184    ·Access快速开发平台下载地址及教程    ·欢迎加入Access专家课堂微信群!    ·如何快速搜索本站文章|示例|资料    
您的位置: 首页 > 技术文章 > ADP及SQL SERVER

在 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条件最简单的语法形式如下:

Where EXISTS (sub query)
假设我们需要返回,所有由销售人员记录的,从年初至今,大于三百万美元的销售订单.为了达到这个效果,我们可以使用如下例子中的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交流群 (群号:54525238)       Access源码网店

常见问答:

技术分类:

相关资源:

专栏作家

关于我们 | 服务条款 | 在线投稿 | 友情链接 | 网站统计 | 网站帮助