在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语句的时候,会作如下的对比:
-
Where子句返回所有EXISTS子句为TRUE的记录.
-
EXIST子句使用相关子查询. 外部查询通过SalesPersonID关联到内部查询.
-
只有SalesYTD大于三百万美元的SalesPersons才会包含在结果之中.
-
如果子查询返回一行或多行记录,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子句,但通常是在一个静态列表,而不是子查询中。