T-SQL 中的CROSS JOIN用法（半翻譯），t-sqlcross

T-SQL 中的CROSS JOIN用法（半翻譯），t-sqlcross

 突然發現個很吊的連結，我們來看看學習資料庫要做些什麼，膽小慎點：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 tempdbgoCREATE 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 CostFROM Product CROSS JOIN SalesItem;

 

結果如下：

 

從結果中可以看到，使用ROW_NUMBER函數產生了對每行產生一個唯一的ID。同時，也通過SalesItem和ID列組合產生新的ProductName列和Cost列。

到目前為止，例子中都是兩表CROSSJOIN，CROSS JOIN 可以用於多表操作，如下：

SELECT * FROM sys.tablesCROSS JOIN sys.objectsCROSS JOIN sys.sysusers;

 

本例中使用了三表進行笛卡兒積，由於結果集列比較多，就不貼出來了，讀者可以自行執行看結果。

 

什麼時候CROSS JOIN可以和INNER JOIN 等同？

在前面提到過，使用CROSS JOIN操作會產生一個笛卡兒積。其實不總是這樣的。當你在CROSS JOIN中使用了WHERE 子句，SQL Server就不會產生笛卡兒積。而是使用普通的JOIN操作。如下：

SELECT * FROM Product P CROSS JOIN SalesItem SWHERE P.ID = S.ProductID; SELECT * FROM Product P INNER JOIN SalesItem SON 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 TotalSalesFROM Product PCROSS JOIN  (SELECT DISTINCT SalesDate FROM SalesItem  ) S1LEFT OUTER JOIN SalesItem S2ON P.ID = S2.ProductIDAND S1.SalesDate = S2.SalesDateGROUP BY S1.SalesDate, P.ProductNameORDER 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 < 1000BEGIN          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);        ENDEND

 

代碼中對每第七個客戶添加銷售記錄。為了示範CROSSJOIN基於結果集導致的體積問題，分別執行下面兩個代碼：

SELECT CONVERT(CHAR(6),S1.SaleDate,112) AS SalesMonth, C.CustName,       ISNULL(SUM(S2.SalesAmt),0) AS TotalSalesFROM Cust CCROSS JOIN (SELECT SaleDate FROM Sales) AS S1LEFT OUTER JOINSales  S2ON C.ID = S2.CustIDAND S1.SaleDate = S2.SaleDateGROUP BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustNameHAVING ISNULL(SUM(S2.SalesAmt),0) = 0ORDER 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 TotalSalesFROM Cust CCROSS JOIN (SELECT DISTINCT SaleDate FROMSales) AS S1LEFT OUTER JOINSales  S2ON C.ID = S2.CustIDAND S1.SaleDate = S2.SaleDateGROUP BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustNameHAVING ISNULL(SUM(S2.SalesAmt),0) = 0ORDER 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的兩邊的順序就要反過來。

說到這裡，暫時沒什麼要添加的了，以後想到再補充。我盡量通過每次文章記錄一下自己的思考和所得，如果一篇文章沒有給自己留下些什麼，不管給別人留下了，也是沒意義的。畢竟你不能預期有多少讀者，但是你知道，肯定有一個讀者——就是自己。希望大家通過文章學到知識的同時，也能認同鄙人的想法。

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。