先进T-SQL的第一阶梯:先进级T-SQL使用交叉连接的入门

作者Gregory Larsen

2016/02/19(第一次发表:2014/12/17)

原文链接：http://www.sqlservercentral.com/articles/Stairway+Series/119933/

正文翻译：

这个阶段的读者应该已经能很好地理解了如何从SQL Server表中查询、更新、插入和删除数据。除此之外，他们还应该掌握一些可以用来控制TSQL代码流的方法，并且能够测试和操作数据。

对于这个新阶梯系列的第一部分，我将讨论CROSS JOIN 操作符。

CROSS JOIN 操作符概论

CROSS JOIN 操作符可以用于将一个数据集中的所有记录合并到另一个数据集中的所有记录中。通过使用两个记录集之间的交叉连接操作符，您将创建一个所谓的笛卡尔积。

这里有一个简单的例子，使用 CROSS JOIN操作符来连接两个表a和B:

 

注意，当使用一个CROSS JOIN操作符时，没有连接两个表的连接子句，就像在两个表之间执行内部（INNER）和外部（OUTER）连接操作时一样。

您需要注意的是，使用CROSS JOIN可以生成一个大的记录集。为了探究这个行为，让我们看两个不同的例子，说明从CROSS JOIN操作中得到的结果集有多大。  

第一个示例，假设您正交叉连接两个表，其中表A有10行，而表B有3行。一个交叉连接的结果集将是10乘以3也就是30行。

第二个示例，假设表A有1,000万行，而表B有300万行。在表A和B之间的交叉连接结果中会有多少行?那将会是惊人的30,000,000亿行。这是一大段行，需要大量的时间和大量的资源来创建结果集。因此，当在大型的记录集上使用CROSS JOIN 操作符时，您需要小心操作。

让我们进一步了解一下使用CROSS JOIN操作符的例子。

使用CROSS JOIN的基本示例

对于前两个示例，我们将加入两个示例表。清单1中的代码将用于创建这两个示例表。确保在用户数据数据库中运行这些脚本，而不是在主服务器中运行这些脚本。

清单1:CROSS JOIN的示例表

 对于第一个CROSS JOIN的示例，我将运行清单2中的代码。

 

清单2:简单的CROSS JOIN示例

 当我在SQL Server Management Studio窗口中运行清单2中的代码时，使用我的会话建立，输出结果的文本，我得到了报告1中的输出结果:

如果你回顾报告1的结果，你可以看到有15个不同的记录。这些前5个记录包含从产品表的第一行与SalesItem表中5个不同的行连接的列值。同样适用于产品表的2秒和3行。返回的行数是Product表中的行数乘以SalesItem表中的行数，即15行。

创建Cartesian产品可能有用的一个原因是生成测试数据。假设我想在我的产品和SalesItem表中使用日期生成一些不同的产品。我可以使用一个交叉连接来实现，如清单3所示:

清单3:简单的交叉连接示例

当我运行清单3中的代码时，我得到了报告2中的输出。

报告2:运行清单3的结果

通过查看清单3中的代码，您可以看到，我生成了一些列，其中包含与产品表中的数据类似的数据。通过使用ROW_NUMBER函数，我可以在每行上生成唯一的ID列。此外，我使用SalesItem表中的ID列创建惟一的ProductName和成本列值。产生的行数等于产品表中的行数乘以SalesItem表中的行数。

到目前为止，本节中的示例只执行了跨两个表的交叉连接。可以使用CROSS JOIN操作符跨多个表执行交叉连接操作。清单4中的示例在三个表中创建了一个Cartesian产品。

 

清单4:使用CROSS JOIN操作符创建三个表的Cartesian产品

运行清单4的输出有两个不同的CROSS_JOIN操作。由该代码创建的Cartesian产品将产生一个结果集，其总行数等于sys中的行数。表乘以sys中的行数。对象乘以sysusers中的行数。

当交叉连接执行类似于内部连接时

在前面的部分中，我提到过，当使用交叉连接运算符时，它会产生一个笛卡尔积。这不是真的。当您使用WHERE子句约束连接到跨连接操作SQL Server的表时，不会创建笛卡尔产品。相反，它的功能类似于普通的连接操作。为了演示这种行为，请查看清单5中的代码。

 

清单5:两个SELECT语句是等价的

清单5中的代码包含两个SELECT语句。第一个SELECT语句使用CROSS JOIN运算符，然后使用WHERE子句定义如何连接CROSS JOIN操作中涉及的两个表。第二个SELECT语句使用带有ON子句的普通INNER JOIN运算符来加入这两个表。 SQL Server的查询优化器非常聪明，知道清单5中的第一个SELECT语句可以重写为INNER JOIN。当CROSS JOIN操作与提供CROSS JOIN中涉及的两个表之间的连接谓词的WHERE子句结合使用时，优化器知道它可以重新编写查询。因此，SQL Server引擎为清单5中的两个SELECT语句生成相同的执行计划。当您不提供WHERE约束时，SQL Server不知道如何连接涉及CROSS JOIN操作的两个表，因此会创建笛卡尔积在与CROSS JOIN操作相关的两组之间。

使用CROSS JOIN查找未销售的产品

前面几节中的例子是帮助你理解CROSS JOIN操作符以及如何使用它。使用CROSS JOIN操作符的功能之一就是使用它来帮助查找一个表中没有另一个表中匹配记录的项目。例如，假设我要报告我的产品表中每个产品名称的总数量和总销售额，以表示我的任何一个产品项目的销售日期。因为在我的例子中，每一个ProductName都不是每天都有一个销售，所以我的报告要求意味着我需要显示那些在某一天没有销售的产品的数量0和总销售额$ 0。这是CROSS JOIN操作符与LEFT OUTER JOIN操作一起使用的地方，可以帮助我识别那些在某一天没有销售的物品。清单6列出了满足这些报告要求的代码：

清单6：查找不使用CROSS JOIN销售的产品

让我引导你通过这个代码。我创建一个子查询，选择所有不同的SalesDate值。这个子查询给我所有的销售日期。然后我与我的产品表CROSS JOIN。这使我可以在每个SalesDate和每个Product行之间创建一个Cartesian产品。从CROSS JOIN返回的集合将在最终结果集中包含我需要的每个值，但每个销售的产品的数量和TotalSalesAmt之和除外。为了得到这些汇总值，我对SalesItem表执行LEFT OUTER JOIN，把它与我用CROSS JOIN操作创建的笛卡尔积相连。我基于ProductID和SalesDate列执行了此连接。通过使用LEFT OUTER JOIN，我的笛卡尔产品中的每一行都会被返回，如果ProductID和SalesDate有一个匹配的SalesDate记录，则Qty和TotalSalesAmt值将与相应的行相关联。这个查询所做的最后一件事是使用GROUP BY子句来总结基于SalesDate和ProductName的Qty和TotalSalesAmount。

性能考虑

生产笛卡尔产品的CROSS JOIN算子有一些性能方面需要考虑。由于SQL引擎需要在一组中的每一行中连接另一组中的每一行，因此结果集可能相当大。如果我使用另一个具有100,000行的表进行CROSS JOIN一个具有1,000,000行的表，那么结果集将具有1,000,000 X 100,000行或100,000,000,000行。这是一个很大的结果集，它将花费大量的时间来创建它。

CROSS JOIN操作员可以成为一个很好的解决方案，用于识别两套所有可能组合的结果集，例如每个月所有客户的销售额，即使在某些月份某些客户没有销售额。使用CROSS JOIN操作符时，如果要优化性能，应尽量减少正在交叉连接的组的大小。例如，假设我有一张包含过去2个月销售数据的表格。如果我想生成一个报告，显示一个月内没有任何销售的客户，那么确定一个月中的天数可以彻底改变我的查询的性能。为了证明这一点，我首先为两个月的时间为1,000位客户创建一组销售记录。我将使用清单7中的代码执行此操作。

清单7:TSQL为性能测试创建示例数据

清单7中的代码为1000个不同的客户创建了两个月的数据。这段代码没有为每7个客户增加销售数据。这段代码产生了1000个Cust表记录和52,338个销售表记录。

为了演示如何使用交叉连接操作符执行不同的操作，这取决于跨连接输入集中使用的集合的大小，让我来运行清单8和清单9中的代码。对于每个测试，我将记录返回的时间

 

清单8:与所有销售记录交叉连接

 

清单9:与不同的销售日期列表交叉连接

在清单8中，CROSS JOIN操作符加入了1000个Cust记录，其中有52,338个销售记录，生成一个创纪录的52338000行的记录集，然后用来确定一个月销售为零的客户。在清单9中，我将选择标准从Sales表中更改为只返回一组不同的SalesDate值。这个独特的集合只产生了61个不同的销售日期值，因此清单9中的CROSS JOIN操作的结果只产生了61,000条记录。通过减少交叉连接操作的结果集，清单9中的查询运行不到1秒，而清单8中的代码在我的机器上运行了19秒。这种性能差异的主要原因是记录SQL Server需要处理每个查询执行的不同操作的数量。如果您查看两个清单的执行计划，您将看到计划略有不同。但是，如果您看一下嵌套循环(Inner Join)操作所生成的记录的数量，在图形化计划的右侧，您将看到清单8估计有52338000条记录，而清单9中的操作仅估计有61,000条记录。这个巨大的记录集，清单8的查询计划从交叉连接嵌套循环操作中生成，然后再传递到几个额外的操作。因为清单8中的所有操作都必须处理5200万的记录。清单8比清单9慢得多。

正如您所看到的，交叉连接操作中使用的记录数可以极大地影响查询运行的时间长度。因此，如果您可以编写您的查询来最小化交叉连接操作中涉及的记录的数量，那么您的查询将执行得更有效率。

结论

交叉连接运算符在两个记录集之间产生一个笛卡尔积。这个操作符有助于识别一个表中没有与另一个表中匹配的记录的项。应注意尽量减少与交叉连接操作符使用的记录集的大小。通过确保交叉连接的结果集尽可能小，您将确保代码运行得和po一样快