目录

关联查询

例子

分解相关的嵌套查询

与不同的表一起使用

关联子查询与内部联接

哪个更快？

哪个更容易阅读？

哪一个更好？

HAVING子句中的关联子查询

在本文中，我们将查看几个示例并将关联子查询与连接进行比较。

关联子查询用于将内部查询的条件与外部查询中的值联系起来。它们是避免“硬编码”值的强大技术。在本文中，查看几个示例并将关联子查询与连接进行比较。

本课的所有示例均基于Microsoft SQL Server Management Studio和AdventureWorks2012数据库。

关联查询

有一些方法可以将外部查询的值合并到子查询的子句中。这些类型的查询称为关联子查询，因为子查询的结果以某种形式连接到外部查询中的值。它们有时称为同步查询。

如果您不知道相关的含义，请查看Google的以下定义：

关联：“具有相互关系或联系，其中一件事影响或依赖于另一件事。”

关联子查询的典型用法是在内部查询的WHERE子句中使用外部查询的列之一。在许多情况下，您希望将内部查询限制为数据子集，这是常识。

例子

我们将提供一个相关的子查询示例，通过报告每个子查询SalesOrderDetail LineTotal，而Average LineTotal代表整体Sales Order。

此请求与我们之前的示例有很大不同，因为我们计算的平均值因每个销售订单而异。

这就是关联子查询发挥作用的地方。我们可以使用外部查询中的值并将其合并到子查询的过滤条件中。

让我们看看我们如何计算平均线总数。为此，我整理了一个插图，显示了带有子查询的SELECT语句。

sql server 子查询和关联查询性能 sql中关联子查询的特点_Sales

进一步阐述图表。该SELECT语句由两部分组成，外部查询和子查询。外部查询用于检索所有SalesOrderDetail行。子查询用于查找和汇总特定SalesOrderID的销售订单详细信息行。

sql server 子查询和关联查询性能 sql中关联子查询的特点_Sales_02

如果我要表达我们将要采取的步骤，我会将它们总结为：

  1. 获取SalesOrderID。
  2. 返回所有SalesOrderID匹配SalesOrderDetail项的LineTotal平均值。
  3. 继续到外部查询中的下一个SalesOrderID并重复步骤1和2。

您可以在数据库中运行的AdventureWork2012查询是：

SELECT SalesOrderID,
       SalesOrderDetailID,
       LineTotal,
       (SELECT AVG(LineTotal)
          FROM   Sales.SalesOrderDetail
         WHERE  SalesOrderID = SOD.SalesOrderID)
                AS AverageLineTotal
FROM   Sales.SalesOrderDetail SOD



以下是查询的结果：

sql server 子查询和关联查询性能 sql中关联子查询的特点_Sales_03

有几点需要指出。

  1. 您可以看到我使用列别名来帮助使查询结果更易于阅读。
  2. 我还为外部查询使用了表别名, SOD。这使得在子查询中使用外部查询的值成为可能。否则，查询不相关！
  3. 使用表别名可以明确每个表中的哪些列。

分解相关的嵌套查询

现在让我们尝试使用SQL将其分解。

首先，假设我们将获得我们的示例SalesOrderDetailID 20。对应的SalesOrderID是43661。

要获得该项的LineTotal平均值很容易：

SELECT AVG(LineTotal)
FROM   Sales.SalesOrderDetail
WHERE  SalesOrderID = 43661



这将返回值2181.765240。

现在我们有了平均值，我们可以将它插入到我们的查询中：

SELECT SalesOrderID,
       SalesOrderDetailID,
       LineTotal,
       <span style="color: #ff0000;">2181.765240 AS AverageLineTotal
FROM   Sales.SalesOrderDetail
WHERE  SalesOrderDetailID = 20



使用子查询，这变成：

SELECT SalesOrderID,
       SalesOrderDetailID,
       LineTotal,
       (SELECT AVG(LineTotal)
          FROM Sales.SalesOrderDetail
         WHERE SalesOrderID = 43661) AS AverageLineTotal
FROM   Sales.SalesOrderDetail
WHERE  SalesOrderDetailID = 20



最后的查询是：

SELECT SalesOrderID,
       SalesOrderDetailID,
       LineTotal,
       (SELECT AVG(LineTotal)
          FROM Sales.SalesOrderDetail
WHERE  SalesOrderID = SOD.SalesOrderID) AS AverageLineTotal
FROM   Sales.SalesOrderDetail AS SOD



与不同的表一起使用

关联子查询，或者就此而言，任何子查询，可以使用与外部查询不同的表。当您使用“父”表时，这可能会派上用场，例如SalesOrderHeader，并且您希望在结果中包含子行的摘要，例如来自SalesOrderDetail。

让我们返回销售订单明细行的OrderDate、TotalDue和数量。为此，我们可以使用下图来了解我们的方位：

sql server 子查询和关联查询性能 sql中关联子查询的特点_Sales_04

为此，我们将在SELECT语句中包含一个关联子查询以返回SalesOrderDetail行数的COUNT。我们将通过过滤外部查询的SalesOrderID来确保计算正确的SalesOrderDetail项。

这是最后的SELECT声明：

SELECT SalesOrderID,
       OrderDate,
       TotalDue,
       (SELECT COUNT(SalesOrderDetailID)
          FROM Sales.SalesOrderDetail
         WHERE SalesOrderID = SO.SalesOrderID) as LineCount
FROM   Sales.SalesOrderHeader SO



结果是：

sql server 子查询和关联查询性能 sql中关联子查询的特点_microsoft_05

此示例需要注意的一些事项是：

  • 子查询从与外部查询不同的表中选择数据。
  • 我使用表和列别名来更容易阅读SQL和结果。
  • 请务必仔细检查您的where子句！如果您忘记在子查询WHERE子句中包含表名或别名，则不会关联查询。

关联子查询与内部联接

重要的是要了解您可以使用子查询或联接获得相同的结果。尽管两者都返回相同的结果，但每种方法都有优点和缺点！

考虑最后一个例子，我们为SalesHeader项计算项的行。

SELECT SalesOrderID,
       OrderDate,
       TotalDue,
       (SELECT COUNT(SalesOrderDetailID)
          FROM Sales.SalesOrderDetail
WHERE SalesOrderID = SO.SalesOrderID) as LineCount
FROM  Sales.SalesOrderHeader SO



可以使用INNER JOIN和GROUP BY来完成相同的查询：

SELECT   SO.SalesOrderID,
         OrderDate,
         TotalDue,
         COUNT(SOD.SalesOrderDetailID) as LineCount
FROM     Sales.SalesOrderHeader SO
         INNER JOIN Sales.SalesOrderDetail SOD
         ON SOD.SalesOrderID = SO.SalesOrderID
GROUP BY SO.SalesOrderID, OrderDate, TotalDue



哪个更快？

您会发现许多人会说要避免子查询，因为它们速度较慢。他们会争辩说，关联子查询必须为外部查询中返回的每一行“执行”一次，而INNER JOIN唯一的子查询必须通过数据。

我呢？我说检查查询计划。我对上面的两个例子都遵循了自己的建议，发现计划是一样的！

这并不是说如果有更多数据，计划就会改变，但我的观点是你不应该只是做出假设。大多数SQL DBMS优化器都非常擅长找出执行查询的最佳方法。他们将采用您的语法，例如子查询或INNER JOIN，并使用它们来创建实际的执行计划。

哪个更容易阅读？

根据您的习惯，您可能会发现该INNER JOIN示例比关联查询更易于阅读。就个人而言，在这个例子中，我喜欢关联子查询，因为它看起来更直接。我更容易看到正在计算的内容。

在我看来，INNER JOIN是不那么直接的。首先，您必须看到所有销售明细行都被返回然后汇总。在您阅读整个声明之前，您不会真正明白这一点。

哪一个更好？

让我知道你的想法。我想听听您是否更愿意使用关联子查询或INNER JOIN示例。

HAVING子句中的关联子查询

与任何其他子查询一样，HAVING子句中的子查询可以与外部查询中的字段相关联。

假设我们进一步按婚姻状况对职位进行分组，并且只想保留那些休假时间大于相应整体婚姻状况的职位和军人身份的组合？

换句话说，我们想回答一个类似于“已婚会计师的平均剩余假期是否比一般已婚员工多？”的问题？

找出答案的一种方法是使用以下查询：

SELECT   JobTitle,
         MaritalStatus,
         AVG(VacationHours)
FROM     HumanResources.Employee AS E
GROUP BY JobTitle, MaritalStatus
HAVING   AVG(VacationHours) > 
            (SELECT AVG(VacationHours)
             FROM   HumanResources.Employee
             WHERE  HumanResources.Employee. MaritalStatus =
                    E.MaritalStatus)



有几点需要指出。首先，请注意我在外部查询中将其Employee别名为“E”。这允许我在内部查询中引用外部表。

此外，对于关联查询，只有在GROUP BY中使用的字段才能在内部查询中使用。例如，对于kicks和grins，我尝试替换MaritalStatus为Gender并得到一个错误。

SELECT   JobTitle,
         MaritalStatus,
         AVG(VacationHours)
FROM     HumanResources.Employee AS E
GROUP BY JobTitle, MaritalStatus
HAVING   AVG(VacationHours) > 
            (SELECT AVG(VacationHours)
             FROM   HumanResources.Employee
             WHERE  HumanResources.Employee. Gender = 
                    E. Gender)



是一个损坏的查询。如果您尝试运行它，您将收到以下错误：


Column ‘HumanResources.Employee.Gender’ is invalid in the HAVING clause 
because it is not contained in either an aggregate function or the GROUP BY clause.