T-SQL 中的CROSS JOIN用法（半翻译）

 突然发现个很吊的链接，我们来看看学习数据库要做些什么，胆小慎点：DBA工作内容！！！！

今天来翻译一篇关于T-SQL的文章，本文可供微软认证70-461:QueryingMicrosoft SQL Server 2012的学习和练习之用。本文以翻译为主，引出个人工作中的一些思考，详见最后部分。

我会尽可能抽时间翻译本系列（见原文出处的相关链接，这是一系列的文章）的其他文章，除了回顾一些知识之外，重点是总结一下自己的所得。

 

-------------------------------------------------------------------以下是译文----------------------------------------------------------------------------

原文出处：http://www.sqlservercentral.com/articles/Stairway+Series/119933/

 

 CROSS JOIN简介：

CROSS JOIN操作符用于把一个数据集中的数据和另外一个数据集中的数据组合在一起。使用CROSS JOIN组合两个数据集的结果也称为笛卡尔积。 举个最简单的CROSS JOIN例子：

SELECT * FROM A CROSS JOIN B

注意当使用CROSS JOIN是，不需要像INNER /OUTER JOIN那样在两表之间加上连接字段，即ON 子句。

另外需要注意的是使用CROSS JOIN可以产生出非常大的数据集。比如上面例子中的A表有10行，B表有3行，那么两表CROSS JOIN之后的结果就有30行。如果A表有1000万行，B表有300万行，那么会有30000000000000行，即30万亿行。这可能会消耗掉SQL Server所有资源用于产生结果集，所以在使用CROSS JOIN是要小心，避免产生不必要的数据。

下面用一些例子来介绍一下CROSS JOIN。

 

使用CROSS JOIN的基本例子：

在这个例子中，我们关联两个简单的表，下面是脚本。请确认脚本运行在TempDB中（注：原文也要求不在master库中创建的，但是基于各种考虑，本人建议放在TempDB中可以通过重新启动SQL Server服务把操作还原，不影响其他库的操作）。

USE tempdb
go
CREATE TABLE Product(ID int,
                      ProductNamevarchar(100),
                      Costmoney);
CREATE TABLE SalesItem(ID int,
                        SalesDate datetime,
                        ProductID int,
                        Qty int,
                        TotalSalesAmt money);
INSERT INTO Product
    VALUES (1,'Widget',21.99),
          (2,'Thingamajig',5.38),
          (3,'Watchamacallit',1.96);
INSERT INTO SalesItem
    VALUES (1,'2014-10-1',1,1,21.99),
          (2,'2014-10-2',3,1,1.96),
          (3,'2014-10-3',3,10,19.60),
          (4,'2014-10-3',1,2,43.98),
           (5,'2014-10-3',1,2,43.98);

 

案例1：简单表的CROSSJOIN：

SELECT * FROM Product CROSS JOIN SalesItem;

结果如下：

 

检查上面的结果可以看到有15行不同的记录，注意不同的含义是只要有一列是不同的，都视为不同。前5行记录包含了来自Product表的第一行，并且关联了SalesItem表中的5行。按同样的道理Product表的2、3行分别与SalesItem表的5行关联。最终结果就是3*5=15行。

使用笛卡儿积的其中一个常见是创建测试数据。比如我们需要从Product和SalesItem表中创建一个不同产品号的产品列表时，可以使用CROSS JOIN，如下代码：

SELECT ROW_NUMBER() OVER(ORDER BY ProductName DESC) AS ID,
       Product.ProductName
       +CAST(SalesItem.ID as varchar(2)) AS ProductName,
       (Product.Cost / SalesItem.ID) * 100 AS Cost
FROM Product CROSS JOIN SalesItem;

 

结果如下：

 

从结果中可以看到，使用ROW_NUMBER函数生成了对每行产生一个唯一的ID。同时，也通过SalesItem和ID列组合产生新的ProductName列和Cost列。

到目前为止，例子中都是两表CROSSJOIN，CROSS JOIN 可以用于多表操作，如下：

SELECT * FROM sys.tables
CROSS JOIN sys.objects
CROSS JOIN sys.sysusers;

 

本例中使用了三表进行笛卡儿积，由于结果集列比较多，就不贴出来了，读者可以自行执行看结果。

 

什么时候CROSS JOIN可以和INNER JOIN 等同？

在前面提到过，使用CROSS JOIN操作会产生一个笛卡儿积。其实不总是这样的。当你在CROSS JOIN中使用了WHERE 子句，SQL Server就不会产生笛卡儿积。而是使用普通的JOIN操作。如下：

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;

代码中的两个SELECT 语句，第一个使用了带有WHERE子句的CROSS JOIN，第二个SELECT 语句使用了常规的INNER JOIN 配合ON子句。SQL Server查询优化器会分析第一个语句，并且重写成第二个语句的形式。因为它知道当带有WHERE子句的CROSS JOIN出现时，可以改写成第二个语句的样子，所以打开两者的执行计划是可以看到相同的结果。但是当没有使用WHERE约束时，SQL Server并不知道如何关联CROSS JOIN的两表，所以只能产生笛卡儿积。

 

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

下面例子用于协助理解CROSS JOIN操作和展示如何使用CROSSJOIN。其中一个常用情景是使用CROSS JOIN操作去查找“在一个表中与另一个表存在不匹配的记录”。比如，假设我需要查询在Product表中，每天每个产品的总数量和销售数量。由于不一定每天所有产品都会销售最少一件，所以可能存在未被销售的产品。对于未销售的产品，需要使用0作为数量，$0作为销售额。这种情况下，可以使用CROSS JOIN组合LEFT OUTER JOIN进行识别，如下：

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;

 

下面来解读一下这个代码，首先，创建一个子查询，查询所有唯一的SalesData值。这个子查询获取所有销售日期。随后，把子查询与Product表进行CROSS JOIN操作，从而产生一个针对每个SalesDate和每个Product的笛卡儿积。这个结果集返回的是除了每个已销售的产品的Qty和TotalSalesAmt之外,所需的最终结果。为了获取这些汇总值，需要使用LEFT OUTER JOIN把SalesItem表关联到前面CROSS JOIN产生的笛卡儿积。两个结果集的关联条件为ProductID和SalesDate列。通过使用LEFT OUTER JOIN，对笛卡儿积中的每一行返回符合ProductID和SalesDate的SalesDate、Qty和TotalSalesAmt值。最后一步就是使用GROUP BY 子句汇总基于SalesDate和ProductName的Qty和TotalSalesAmount值。 

性能考虑

CROSS JOIN操作由于可能产生笛卡儿积会存在一定的性能风险。因为SQL 引擎需要把两表的每一行都一一匹配并产生新数据，这个结果集可能非常巨大。如果一个有100万行的表与一个有10万行的表CROSS JOIN，结果集就是1000000*100000=100000000000 (1万亿！)。SQL Server需要花费大量的时间和资源去创建这个结果集。

但是CROSS JOIN又可以用于分析两个结果集的可能结果，比如每个月所有客户的所有销售情况，即使某些客户在某些月没有销售记录，也可以查询出对应的情况。当使用CROSS JOIN操作时，尽可能最小化CROSS JOIN的源数据和结果集，以便使性能可控。假设有一个表存放了最近2个月的销售记录，如果我需要生成一个关于哪些客户在一个月内完全没购买记录的报表时，就可以用CROSS JOIN。下面来演示一下：

首先创建两个表，有1000个客户的，存储了2个月的销售信息。

CREATE TABLECust (Id int, CustName varchar(20));
CREATE TABLESales (Id int identity
                   ,CustID int
                                      ,SaleDate date
                                      ,SalesAmt money);
SET NOCOUNT ON;
DECLARE @I int = 0;
DECLARE @Date date;
WHILE @I < 1000
BEGIN  
        SET @I = @I + 1;
        SET @Date = DATEADD(mm, -2, '2014-11-01');
        INSERT INTO Cust
        VALUES (@I,
                'Customer #' + right(cast(@I+100000 as varchar(6)),5));
        WHILE @Date < '2014-11-01'
        BEGIN
               IF @I%7 > 0
                       INSERT INTO Sales (CustID, SaleDate, SalesAmt)
                       VALUES (@I, @Date, 10.00);
               SET @Date = DATEADD(DD, 1, @Date);
        END
END

 

代码中对每第七个客户添加销售记录。为了演示CROSSJOIN基于结果集导致的体积问题，分别执行下面两个代码：

SELECT CONVERT(CHAR(6),S1.SaleDate,112) AS SalesMonth, C.CustName,
       ISNULL(SUM(S2.SalesAmt),0) AS TotalSales
FROM Cust C
CROSS JOIN 
(
SELECT SaleDate FROM Sales
) AS S1
LEFT OUTER JOIN
Sales  S2
ON C.ID = S2.CustID
AND S1.SaleDate = S2.SaleDate
GROUP BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustName
HAVING ISNULL(SUM(S2.SalesAmt),0) = 0
ORDER BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustName
 
SELECT CONVERT(CHAR(6),S1.SaleDate,112) AS SalesMonth, C.CustName,
       ISNULL(SUM(S2.SalesAmt),0) AS TotalSales
FROM Cust C
CROSS JOIN 
(
SELECT DISTINCT SaleDate FROMSales
) AS S1
LEFT OUTER JOIN
Sales  S2
ON C.ID = S2.CustID
AND S1.SaleDate = S2.SaleDate
GROUP BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustName
HAVING ISNULL(SUM(S2.SalesAmt),0) = 0
ORDER BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustName

 

在第一个查询中，1000个客户和52338个销售记录产生了52338000行结果集。在第二个查询中，仅对具有唯一SalesData值的数据进行笛卡儿积，此处仅产生了61行唯一的SalesData数据，所以CROSS JOIN之后的的结果集只有61000行。通过控制CROSS JOIN的输入规模，第二个查询只需要1秒，而第一个查询在本机需要19秒。可见输入集的规模对性能有很明显的影响。检查两者的执行计划也可以看到明显的不同。

所以在编写CROSS JOIN时，需要考虑输入集的规模，从而避免性能问题。

 

结论：

CROSS JOIN操作会在两个记录集中进行笛卡儿积操作，这个操作在检查“存在于一个表但不存在于另外一个表”时非常有用。但是需要非常小心用于CROSS JOIN的输入集的规模。把CROSS JOIN的输入集尽可能控制在必要的规模，以便保证运行效率。

-------------------------------------------------------------------个人感悟----------------------------------------------------------------------------

按照惯例，总要说些自己的看法。毕竟是以原创发布，不能简单地翻译。

首先，本文主体还是翻译，但是不是全译。读者可以自行查看原文。

其次，在这么多年的工作中，我用CROSS JOIN的确很少，因为我知道它会带来的问题。一直以来，我用这个功能的唯一目的是用来产生一个大的结果集，主要拿来测试。比如我要测试1亿行数据的某些写法、索引性能时，会用一个10000行的表互相CROSS JOIN，一亿行数据就出来了，然后随心所欲。

通常来说，我不建议在正式环境中使用CROSS JOIN，因为大部分情况下这是不合理的，但是在工作过程中，的确见到一些系统，为了产生某些结果（如报表），需要关联两个本来无关联关系的表，这样几乎只能用CROSS JOIN。当然，如果有可能，尽量使两表有关联，或者转用数据仓库的一些技术。

最后，我们应该透过现象看本质，文中提到有ON和没有ON的CROSS JOIN之间的差异，我以前见过论坛有人说过，INNER JOIN也是先CROSS JOIN 再筛选，我当时就觉得不可能，但是没有具体证据不好说什么。关系型数据库出现了接近40年，今时今日的RDBMS已经没有太多的差异，更多的是修修补补和提升一点点的性能而已。很多时候我们不能想当然，而应该用实际操作实践一下自己的想法，比如过去如果我会用前面提到的例子实践一下的话，当时我就可以告诉那个人，INNER JOIN并不是简单地先CROSS JOIN再筛选，不然太反人类了。

        读者是否会想想为什么会有这篇文章？其实CROSS JOIN用得较少，并且也比较危险，在真正工作中使用得其实还是很少的。可是偏偏在微软认证中出现了（大家先别黑别人，多想想为什么，CROSS JOIN并不仅仅存在于SQL Server）。正如前面说过，除了产生测试数据之外，很多情况下（论坛回帖过程中也出现过），有些业务需求的确要用CROSS JOIN来生成，虽然这些情况大部分是可以通过改变设计来实现，不过现实情况就是很少系统有足够的前期设计，大部分都是后期修修补补。所以从这个用法中我们更多地应该思考如何通过设计来减少这些情况。设计需要知识和经验，强求不来，读者（包括自己），也应该从中多考虑，多总结，并且记录下来以便真的有一天需要设计的时候可以用到。

还有一个，过去，我只会记住CROSS JOIN就是把A/B两表的每一行关联一次，但是却没有想过CROSS JOIN两边的次序会带来什么影响。重点是结果集的展示，左边的表会出现在最前面，并且左边的每一个数据与右边每行分别匹配，也就是说是N个左表的相同行和分别右表的单独行，如果所需的结果是反过来，那么CROSS JOIN的两边的顺序就要反过来。

说到这里，暂时没什么要添加的了，以后想到再补充。我尽量通过每次文章记录一下自己的思考和所得，如果一篇文章没有给自己留下些什么，不管给别人留下了，也是没意义的。毕竟你不能预期有多少读者，但是你知道，肯定有一个读者——就是自己。希望大家通过文章学到知识的同时，也能认同鄙人的想法。