高级T-SQL进阶系列 （一）【中篇】：使用 CROSS JOIN 介绍高级T-SQL

【译注：此文为翻译，由于本人水平所限，疏漏在所难免，欢迎探讨指正】

原文连接：传送门。

当一个CROSS JOIN 表现得如同一个INNER JOIN

在上一章节我提到当你使用一个CROSS JOIN 操作符时候它会产生一个笛卡尔积。然而这并不总是真实的情况。当你使用WHERE 子句对涉及到CROSS JOIN操作符的表的关联进行约束，SQL SERVER便不会产生笛卡尔积。反而它的功能更像是一个正常的 JOIN 操作符。为了演示这种行为，请查看列表5的代码：

SELECT * FROM Product P CROSS JOIN SalesItem S
WHERE P.ID = S.ProductID;
SELECT * FROM Product P INNER JOIN SalesItem S
ON P.ID = S.ProductID;

  列表5：两个相等的SELECT语句

列表5的代码包含了两个SELECT语句。第一个语句使用了CROSS JOIN操作符然后使用了一个WHERE子句来定义如何连接CROSS JOIN操作符涉及的两个表。第二个SELECT语句使用了带有一个ON子句的正常的INNER JOIN操作符来关联两个表。SQL SERVER的查询优化器足够智能，可以知道列表5中的第一个SELECT语句可以重写为一个INNER JOIN。一个WHERE子句在此提供了CROSS JOIN操作符涉及的两个表的连接谓词，因此当一个CROSS JOIN操作符和一个WHERE子句配合起来使用的时候，查询优化器便知道它可以重写这个查询。因此SQL SERVER引擎为列表5中的两个查询生成了相同的执行计划。而当你不提供一个WHERE约束的时候，SQL SERVER不知道如何关联CROSS JOIN子句涉及的两个表，于是便在CROSS JOIN操作符相关的两个数据集之间创建了笛卡尔积。

 使用CROSS JOIN来找到未卖出的产品

前面章节出现的示例可以帮助你理解CROSS JOIN操作符以及如何使用它。使用CROSS JOIN操作符的力量之一便是用其找到存在于一个表而在另一张表中没有匹配记录的那些条目。举个例子，假设我想要报告Product表中的每个产品每天的销售量和销售金额，因为在我的例子中，每个ProductName每天没有被销售也会有一个记录。我的报告需求意味着我需要为那些在某一个给定的日期中没有销售的产品展示一个数量0和销售金额为0的记录。而CROSS JOIN操作符与LEFT OUTER JOIN操作符配合起来使用恰好可以帮助我定义这些在某一天没有售出的条目。满足这些报告需求的代码可以在列表6中被找到：

SELECT S1.SalesDate, ProductName
     , ISNULL(Sum(S2.Qty),0) AS TotalQty
     , ISNULL(SUM(S2.TotalSalesAmt),0) AS TotalSales
FROM Product P
CROSS JOIN  
(
SELECT DISTINCT SalesDate FROM SalesItem
  ) S1
LEFT OUTER JOIN 
SalesItem S2
ON P.ID = S2.ProductID
AND S1.SalesDate = S2.SalesDate
GROUP BY S1.SalesDate, P.ProductName
ORDER BY S1.SalesDate;

列表6：使用CROSS JOIN找到没有销售的产品

让我来带你分析这段代码。首先我创建了一个子查询，它获取了所有去重的SalesDate的值。这个子查询给了我所有发生交易的日期。然后我用Product表与其进行CROSS JOIN，这允许我在每个日期和每种产品行之间创建一个笛卡尔积。从CROSS JOIN返回的数据集将会包含我在最终结果集合中需要的每个值--当然除了每个售出产品的Qty和TotalSalesAmt 。为了得到这些汇总列我将CROSS JOIN得到的笛卡尔积与SalesItem表进行了一个LEFT OUTER JOIN关联。我基于ProductID和ProductID列进行了这个关联。通过使用LEFT OUTER JOIN，笛卡尔积中的每一行数据都会被返回，并且如果对于一个ProductID和SalesDate具有一个匹配的SalesDate记录，那么Qty和TotalSalesAmt 值便会被关联在期望的行中。这个查询做的最后一件事便是使用GROUP BY子句来基于SalesDate和ProductName来对Qty 和TotalSalesAmount 进行总结。