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;