DATABASE SYSTEM CONCEPTS5.13

 对于这个新的楼梯系列的第一部分，我将讨论交叉连接操作符。交叉连接算子简介交叉连接操作符可用于将一个数据集中的所有记录组合到另一个数据集中的所有记录。通过在两组记录之间使用交叉连接运算符，您正在创建所谓的笛卡尔积。下面是使用交叉连接运算符连接两个表A和B的简单示例：
注意，在使用交叉连接操作符时，没有连接两个表的JOIN子句，就像在两个表之间执行内部和外部连接操作时一样。您需要注意，使用交叉连接可以生成一个大型记录集。为了探索这种行为，让我们看两个不同的例子，说明交叉连接操作的结果集有多大。对于第一个例子，假设您交叉连接两个表，其中表A有10行，表B有3行。交叉连接的结果集为10乘3或30行。对于第二个例子，假设表A有1000万行，表B有300万行。表A和表B之间的交叉连接结果集中将有多少行？这将是一个惊人的3万，000，000，000行。这是很多行，创建结果集需要SQL Server大量的时间和大量资源。因此，在大型记录集上使用交叉联接运算符时，您需要小心。让我们通过研究几个例子来进一步研究如何使用交叉联接操作符。交叉连接使用基本示例对于前几个例子，我们将加入两个示例表。清单1中的代码将用于创建这两个示例表。确保在用户数据数据库中运行这些脚本，而不是在主数据库中运行这些脚本。
清单1：交叉连接的示例表对于第一个交叉联接示例，我将运行清单2中的代码。
清单2：简单的交叉连接示例当我在SQLServerManagementStudio窗口中运行清单2中的代码时，使用会话设置以文本输出结果时，我在报告1中获得了输出：

Report 1：运行清单2时的结果如果您查看报告1中的结果，您可以看到有15种不同的记录。前5个记录包含与SalesItem表中的5个不同行连接的Producttable第一行的列值。对于Producttable的2秒和3行也是如此。返回的行总数是Producttable中的行数乘以SalesItem表中的行数(15行)。创建笛卡尔产品的一个原因可能是生成测试数据。假设我想使用ProductandSalesItem表中的日期生成许多不同的产品。我可以使用交叉连接来实现这一点，如清单3所示：

 清单3：简单的交叉连接示例当我运行清单3中的代码时，我得到了报告2中的输出
Report 2：运行清单3时的结果正如您可以看到的，通过查看清单3中的代码，我生成了许多行，其中包含类似于我的Producttable中的数据的数据。通过使用ROW_Number函数，我能够在每一行上生成唯一的ID列。此外，我还使用SalesItem表中的ID列来创建唯一的ProductName和成本列值。生成的行数等于Producttable中的行数乘以SalesItem表中的行数。到目前为止，本节中的示例只对两个表执行了交叉连接。可以使用交叉连接运算符跨多个表执行交叉连接操作。清单4中的示例在三个表中创建了一个笛卡尔产品。
清单4：使用交叉连接操作符创建三个表的笛卡尔积运行清单4的输出有两个不同的交叉_连接操作。从此代码创建的笛卡尔产品将产生一个结果集，其总行数将等于sys.table中的行数乘以sys.objects中的行数乘以sys.sysuserts中的行数。当交叉连接像内部连接一样执行时在上一节中，我提到当您使用交叉连接运算符时，它将生成一个笛卡尔乘积。这不是一直都是这样的。当您使用限制交叉连接操作中涉及的表的联接的WHERE子句时，SQLServer不会创建笛卡尔产品。相反，它的功能类似于普通的联接操作。要演示这种行为，请查看清单5中的代码。
清单5：两个等效的SELECT语句。清单5中的代码包含两个SELECT语句。第一个SELECT语句使用交叉连接操作符，然后使用WHERE子句定义如何连接交叉连接操作中涉及的两个表。第二个SELECT语句使用带ON子句的普通内部联接运算符连接两个表。SQLServer的查询优化器非常聪明，可以知道清单5中的第一个SELECT语句可以重写为内部联接。当交叉连接操作与WHERE子句一起使用时，优化器知道可以重写查询。WHERE子句在交叉连接所涉及的两个表之间提供连接谓词。因此，SQLServer引擎为清单5中的两个SELECT语句生成相同的执行计划。当您不提供WHERE约束时，SQLServer不知道如何连接涉及交叉连接操作的两个表，因此它在与交叉连接操作关联的两个集合之间创建一个笛卡儿积。使用交叉连接查找未售出的产品前面几节中的示例帮助您理解交叉连接操作符以及如何使用它。使用交叉连接操作符的一个功能是使用它帮助在一个表中查找另一个表中没有匹配记录的项。例如，假设我想报告每个ProductName的总数量和总销售额，在ProductName的每个日期，我的任何一个产品项目被出售。由于在我的示例中，每个ProductName并不是每天都有销售，所以我的报告要求意味着对于那些在某一天没有售出的产品，我需要显示0的数量和0美元的总销售额。在这里，交叉连接操作符与左外部连接操作一起将帮助我识别那些在给定的一天内没有售出的物品。满足这些报告要求的代码可以在清单6中找到：
清单6：查找未使用交叉连接销售的产品让我给你介绍一下这段代码。我创建一个子查询，它选择所有不同的SalesDate值。这个子查询提供了所有销售日期。然后，我将它与我的ProductTable连接起来。这允许我在每个SalesDate和每个Productrow之间创建一个笛卡儿产品。从交叉连接返回的集合将有我在最终结果集中所需的所有值，除了每个售出产品的Qty和TotalSalesAmt之和。为了获得这些摘要值，我对SalesItem表执行一个左外部联接，将它与我用交叉联接操作创建的笛卡尔积连接起来。我基于Productid和SalesDate列执行了这个连接。通过使用左外部联接，我的笛卡尔产品中的每一行都将被返回，如果Productid和SalesDate有一个匹配的SalesDate记录，那么Qty和TotalesAmt值将与适当的行相关联。这个查询所做的最后一件事是使用GROUPBY子句来总结基于SalesDate和ProductName的Qty和TotalesA挂载。绩效考虑产生笛卡尔乘积的交叉连接算子有一些性能方面需要考虑。因为SQL引擎需要将一组中的每一行与另一集中的每一行连接起来，所以结果集可能相当大。如果我做一个交叉连接，一个表有1，000，000行，另一个表有100，000行，那么我的结果集将有1，000，000，000行，或100，000，000行。这是一个很大的结果集，创建它需要大量时间。交叉连接操作符可以是一个很好的解决方案，可以在两个集合的所有可能组合中识别一个结果集，就像每个月所有客户的所有销售一样，即使有几个月有些客户没有销售。当使用交叉连接运算符时，如果要优化性能，则应尽量减少交叉连接集的大小。例如，假设我有一个表，其中包含过去两个月的销售数据。如果我想要生成一个显示一个月内没有销售的客户的报告，那么识别一个月中的天数的方法可能会极大地改变我的查询的性能。为了演示这一点，让我首先为1000名客户创建一套为期两个月的销售记录。我将使用清单7中的代码来完成这个任务。

 清单7：创建用于性能测试的示例数据的TSQL清单7中的代码为1，000个不同的客户创建了2个月的数据。此代码没有为每七个客户添加任何销售数据。此代码生成1，000条CUST表记录和52，338条销售表记录。为了演示如何使用交叉连接操作符，取决于在交叉连接输入集中使用的集合的大小，让我运行清单8和清单9中的代码。对于每个测试，我将记录返回结果所需的时间。
清单8：交叉连接所有销售记录
清单9：针对销售日期的不同列表交叉连接在清单8中，交叉连接操作符将1，000条Cust记录与52，338条销售记录连接起来，生成52，338，000行的记录集，然后用于确定一个月内零销售额的客户。在清单9中，我将选择条件从Sales表更改为只返回一组不同的SalesDate值。这个不同的集合只产生61个不同的SalesDate值，因此清单9中的交叉连接操作的结果只生成61，000条记录。通过减少交叉连接操作的结果集，清单9中的查询在1秒内运行，而清单8中的代码在我的机器上运行时间为19秒。造成这种性能差异的主要原因是SQL Server需要为每个查询执行的不同操作处理大量记录。如果您查看这两个清单的执行计划，您会发现计划略有不同。但是，如果您查看从嵌套循环(内连接)操作生成的估计记录数量，在图形计划的右侧，您将看到清单8估计了52，338，000条记录，而清单9中的相同操作仅估计了61，000条记录。清单8的查询计划从交叉连接嵌套循环操作生成的大型记录集然后传递到几个附加操作。因为清单8中的所有这些操作都必须针对5200万条记录工作。清单8比清单9慢得多。如您所见，在交叉连接操作中使用的记录数量可能会极大地影响查询运行的时间长度。因此，如果您可以编写查询以最小化交叉连接操作中涉及的记录数量，则查询的执行效率将大大提高。结语交叉连接运算符在两个记录集之间产生一个笛卡尔积。此操作符有助于识别一个表中没有匹配记录的项。应注意尽量减少与交叉连接操作符一起使用的记录集的大小。通过确保交叉连接的结果集尽可能小，您将确保代码运行得越快。问答在本节中，您可以通过回答以下问题来回顾如何使用交叉联接运算符来理解。问题1：交叉联接运算符根据ON子句中指定的列，通过匹配两个记录集来创建结果集。(对还是错)？千真万确假的问题2：当表A和表B包含重复行时，哪一个公式可以用来标识从两个表A和B之间的无约束交叉连接返回的行数？表A中的行数乘以表B中的行数表A中的行数乘以表B中的唯一行数表A中的唯一行数乘以表B中的行数表A中唯一行数乘以表B中唯一行数问题3：哪种方法提供了减少交叉连接操作产生的笛卡尔产品的最佳机会？确保连接的两个集合尽可能多行。确保连接的两个集合尽可能少行。确保交叉连接操作左边的设置尽可能少行。确保交叉连接操作右侧的设置尽可能少行。答案：问题1：正确的答案是b。交叉连接运算符不使用ON子句来执行交叉连接操作。它将一个表中的每一行连接到另一个表中的每一行。当交叉连接连接两个集合时，它创建了一个笛卡尔积。问题2：正确的答案是A、b、c和d，因为如果表A或B中有重复行，则在为交叉联接操作创建笛卡尔积时，每个重复行都是联接。问题3：正确的答案是b。通过减少交叉连接操作中涉及的两个集合的大小，使由交叉连接操作创建的最终集的大小最小化。c和d还有助于减小交叉连接操作创建的最终集的大小，但并不像确保涉及到交叉连接操作的两个集合的行数最少一样优化。本文是高级T-SQL楼梯的一部分。注册我们的RSS提要，并得到通知，一旦我们发布一个新的层次在楼梯上！

 清单3：HAVING子句中的相关子查询

当我运行清单5中的代码时，我在Report 3中得到了结果。
报告3：运行清单3的结果

清单3中的相关子查询代码使用相关子查询中外部查询中来自GROUP BY子句的CustomerID。对于从GROUP BY子句返回的每一行，相关子查询将执行一次。这允许HAVING子句通过汇总每个SalesOrderHeader记录中的记录与外部查询中的CustomerID相关联的SubTotal列的值，从外部查询中计算出售给每个CustomerID的产品总量。清单3中的Transact-SQL语句仅返回CustomerID已购买价值超过$ 150,000的产品的行。

包含相关子查询的UPDATE语句的示例

相关的子查询不仅可用于使用SELECT语句返回结果集。您也可以使用它们来更新SQL Server表中的数据。为了演示这一点，我将首先使用清单4中的代码在tempdb表中生成一些测试数据。

报告2：运行清单2中的代码时出错


我得到了报告2中显示的错误，因为我的相关子查询包含对外部查询列中OH.SalesOrderID列的引用。由于所有相关的子查询都引用来自外部查询的一个或多个列，因此您无法独立于与其关联的外部查询来运行它们。事实上，无法独立于整个Transact-SQL语句运行子查询是区分与正常子查询相关的子查询。


这里介绍的例子是在WHERE子句中使用相关子查询的一个非常简单的例子。希望通过这样一个简单的例子，它很容易理解正常子查询和相关子查询之间的区别。通常，相关的子查询可能会相当复杂。此外，请记住，可能还有其他方法可以在不使用相关子查询的情况下满足业务需求。


正如您所看到的，编写相关子查询与正常子查询非常相似，但您无法独立于外部查询运行相关子查询。


HAVING子句中相关子查询的示例


有时您可能想通过来自外部查询的不同值限制HAVING子句。这是您可以在HAVING子句中使用相关子查询的时候。假设您必须编写一个查询计算2008年在税前购买价值超过150000美元的产品的客户的回扣金额。清单3中的代码通过使用相关子查询计算这些有价值客户的回扣金额HAVING条款。

 什么是相关子查询？

在这个楼梯的第2层，我们了解到，正常的子查询只是另一个Transact-SQL语句中的SELECT语句，如果独立于外部查询运行，子查询可以返回结果。相关子查询是不能独立于外部查询运行的子查询的一种形式，因为它包含一个或多个来自外部查询的列。相关子查询就像普通的子查询一样，有时被称为内部查询。如果相关子查询（内部查询）独立于外部查询运行，它将返回一个错误。由于内部查询的执行取决于来自外部查询的值，因此称为相关子查询。

相关的子查询可能会执行很多次。它将为外部查询中选定的每个候选行运行一次。每个候选行的列值将用于为相关子查询的每次执行提供内部的外部查询列的值。包含相关子查询的语句的最终结果将基于相关子查询的每次执行结果。

相关子查询示例的示例数据

为了演示如何使用相关的子查询，我需要一些测试数据。我的所有示例都将使用AdventureWorks2008R2数据库，而不是创建自己的测试数据。如果你想跟随并在你的环境中运行我的例子，那么你可以从这里下载AdventureWorks2008R2数据库：http://msftdbprodsamples.codeplex.com/releases/view/93587

概要


相关子查询是一个包含来自外部查询的一个或多个列的内部查询。相关子查询对外部查询的每个候选行都执行一次。由于相关子查询包含来自外部查询的列，因此无法独立于外部查询运行它。相关的子查询有它们的位置，但在外部查询中标识了大量候选行时，从性能角度来看，它们的伸缩性不好。


问题和答案


在本节中，您可以通过回答以下问题来查看您是否理解相关子查询的概念。


问题1：


在编写相关的子查询时，你需要有____________________。 （填空）
一个。内部查询中的一列或多列用于限制相关子查询的结果。
湾相关子查询的选择列表中使用的内部查询中的一列或多列。
C。来自外部查询的一列或多列用于限制相关子查询的结果。
d。在相关子查询的选择列表中使用的外部查询中的一个或多个列。


问题2：


选择以下关于相关子查询的所有语句。
一个。随着候选行数的增加，包含相关子查询的Transact-SQL语句的性能得到提高。
湾相关子查询将针对来自外部查询的每个候选行执行一次。
C。相关子查询将引用内部查询中的一个或多个列。
d。在HAVING子句中使用相关子查询时，对于由GROUP BY子句返回的每个候选行，内部查询将执行一次。


问题3：


相关的子查询就像普通的子查询，相关的子查询可以独立于整个Transact-SQL语句（True或False）运行。
一个。真正
湾假


回答：


问题1：


正确答案是c。相关子查询需要来自外部查询的一列或多列用于相关子查询语句中。在执行相关子查询时，这些外部列引用被替换为每个候选行的值。


问题2：


正确答案是b和d。 a不正确，因为随着候选行数的增加，相关子查询会执行更多次，并且Transact-SQL语句性能会变差。 c不正确，因为相关子查询必须包含来自外部查询的一行或多行，而不是内部查询。


问题3：


正确答案是b。如果尝试独立于完整的Transact-SQL语句运行相关子查询，则相关子查询语句将失败。

翻译自《DATABASE SYSTEM CONCEPTS》  Abraham Silberschatz   Henry F.Korth   S.Sudarshan