CROSS APPLY 產生外連接

在回答SQL 計算不同分區時， Erik Darling 發布了此程式碼以解決以下問題COUNT(DISTINCT) OVER ()：

SELECT      *
FROM        #MyTable AS mt
CROSS APPLY (   SELECT COUNT(DISTINCT mt2.Col_B) AS dc
               FROM   #MyTable AS mt2
               WHERE  mt2.Col_A = mt.Col_A
               -- GROUP BY mt2.Col_A 
           ) AS ca;

查詢使用CROSS APPLY(not OUTER APPLY) 那麼為什麼執行計劃中有外連接而不是內連接？

另外，為什麼取消註釋 group by 子句會導致內部聯接？

我認為數據並不重要，但複製了 kevinwhat 在另一個問題上給出的數據：

create table #MyTable (
Col_A varchar(5),
Col_B int
)

insert into #MyTable values ('A',1)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',3)

insert into #MyTable values ('B',4)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',5)

概括

SQL Server 使用正確的聯接（內部或外部）並在必要時添加投影，以在執行apply和join之間的內部轉換時遵守原始查詢的所有語義。

計劃中的差異都可以通過SQL Server 中帶有和不帶有 group by 子句的聚合的不同語義來解釋。

---

細節

加入與申請

我們需要能夠區分apply和join：

• 申請

apply的內部（下部）輸入針對外部（上部）輸入的每一行執行，其中一個或多個內側參數值由目前外部行提供。應用的總體結果是參數化內側執行產生的所有行的組合（全部聯合）。參數意味著apply的存在有時被稱為相關連接。

應用總是由嵌套循環運算符在**執行計劃中實現。運算符將具有外部引用屬性而不是連接謂詞。外部引用是在循環的每次迭代中從外側傳遞到內側的參數。

• 加入

連接在連接運算符處評估其連接謂詞。連接通常可以通過SQL Server 中的Hash Match、Merge或Nested Loops運算符來實現。

當選擇了嵌套循環時，可以通過缺少外部引用（並且通常存在連接謂詞）將其與應用區分開來。連接的內部輸入從不引用來自外部輸入的值 - 對於每個外部行，內部仍然執行一次，但內部執行不依賴於目前外部行中的任何值。

有關更多詳細資訊，請參閱我的文章Apply 與 Nested Loops Join。

…為什麼執行計劃中有外連接而不是內連接？

當優化器將應用轉換為連接（使用名為 的規則ApplyHandler）以查看它是否可以找到更便宜的基於連接的計劃時，就會出現外連接。當應用程序包含標量聚合時，連接必須是外部連接以確保正確性。正如我們將看到的，內連接不能保證產生與原始**應用相同的結果。

標量和向量聚合

• 沒有相應GROUP BY子句的聚合是標量聚合。
• 帶有相應GROUP BY子句的聚合是向量聚合。

在 SQL Server 中，標量聚合將始終生成一行，即使它沒有指定要聚合的行。例如，COUNT沒有行的標量聚合為零。沒有行的向量 COUNT聚合是空集（根本沒有行）。

以下玩具查詢說明了差異。您還可以在我的文章Fun with Scalar and Vector Aggregates中閱讀有關標量和矢量聚合的更多資訊。

-- Produces a single zero value
SELECT COUNT_BIG(*) FROM #MyTable AS MT WHERE 0 = 1;

-- Produces no rows
SELECT COUNT_BIG(*) FROM #MyTable AS MT WHERE 0 = 1 GROUP BY ();

db<>小提琴展示

轉型申請加入

我之前提到過，當原始應用包含標量聚合時，連接必須是外部連接以確保**正確性。為了詳細說明為什麼會出現這種情況，我將使用問題查詢的簡化範例：

DECLARE @A table (A integer NULL, B integer NULL);
DECLARE @B table (A integer NULL, B integer NULL);

INSERT @A (A, B) VALUES (1, 1);
INSERT @B (A, B) VALUES (2, 2);

SELECT * FROM @A AS A
CROSS APPLY (SELECT c = COUNT_BIG(*) FROM @B AS B WHERE B.A = A.A) AS CA;

column 的正確結果c為零**，因為它COUNT_BIG是一個標量**聚合。當將此應用查詢轉換為聯接表單時，SQL Server 會生成一個內部替代方案，如果它以 T-SQL 表示，則類似於以下內容：

SELECT A.*, c = COALESCE(J1.c, 0)
FROM @A AS A
LEFT JOIN
(
   SELECT B.A, c = COUNT_BIG(*) 
   FROM @B AS B
   GROUP BY B.A
) AS J1
   ON J1.A = A.A;

要將 apply 重寫為不相關連接，我們必須GROUP BY在派生表中引入 a（否則可能沒有A要連接的列）。聯接必須是外部聯接，以便表中的每一行@A繼續在輸出中生成一行。當連接謂詞不為真時，左連接將產生一個NULLfor 列。需要將其轉換為零才能完成從apply的正確轉換c。NULL``COALESCE

下面的展示展示瞭如何使用連接作為原始應用查詢COALESCE來生成與外部連接相同的結果：

db<>小提琴展示

隨著GROUP BY

…為什麼取消註釋 group by 子句會導致內部聯接？

繼續簡化範例，但添加GROUP BY：

DECLARE @A table (A integer NULL, B integer NULL);
DECLARE @B table (A integer NULL, B integer NULL);

INSERT @A (A, B) VALUES (1, 1);
INSERT @B (A, B) VALUES (2, 2);

-- Original
SELECT * FROM @A AS A
CROSS APPLY 
(SELECT c = COUNT_BIG(*) FROM @B AS B WHERE B.A = A.A GROUP BY B.A) AS CA;

現在COUNT_BIG是一個向量聚合，因此空輸入集的正確結果不再為零，它根本不是行。換句話說，執行上面的語句不會產生任何輸出。

從apply轉換為join時，這些語義更容易遵守，因為CROSS APPLY自然會拒絕任何不生成內側行的外側行。因此，我們現在可以安全地使用內連接，而無需額外的表達式投影：

-- Rewrite
SELECT A.*, J1.c 
FROM @A AS A
JOIN
(
   SELECT B.A, c = COUNT_BIG(*) 
   FROM @B AS B
   GROUP BY B.A
) AS J1
   ON J1.A = A.A;

下面的展示顯示內部連接重寫產生的結果與使用矢量聚合的原始應用相同：

db<>小提琴展示

優化器碰巧選擇了與小表的合併內連接，因為它很快找到了一個便宜的連接計劃（找到了足夠好的計劃）。基於成本的優化器可能會繼續將連接重寫為應用 - 可能會找到更便宜的應用計劃，如果使用循環連接或 forceeek 提示，它會在這裡 - 但在這種情況下不值得付出努力。

筆記

簡化範例使用不同內容的不同表格，更清楚地顯示語義差異。

有人可能會爭辯說，優化器應該能夠推斷自聯接不能生成任何不匹配（非聯接）的行，但它今天不包含該邏輯。無論如何，不能保證在查詢中多次訪問同一個表通常會產生相同的結果，具體取決於隔離級別和並發活動。

優化器擔心這些語義和邊緣情況，因此您不必擔心。

---

獎金：內部申請計劃

SQL Server可以為範例查詢生成內部應用計劃（不是內部連接計劃！），它只是出於成本原因選擇不這樣做。問題中顯示的外部連接計劃的成本是我筆記型電腦的 SQL Server 2017 實例上的0.02898個單位。

您可以使用未記錄和不受支持的跟踪標誌 9114（禁用等）來強制應用ApplyHandler（相關連接）計劃，僅用於說明：

SELECT      *
FROM        #MyTable AS mt
CROSS APPLY 
(
   SELECT COUNT_BIG(DISTINCT mt2.Col_B) AS dc
   FROM   #MyTable AS mt2
   WHERE  mt2.Col_A = mt.Col_A 
   --GROUP BY mt2.Col_A
) AS ca
OPTION (QUERYTRACEON 9114);

這會產生一個帶有惰性索引假離線的*應用嵌套循環計劃。*總估計成本為0.0463983（高於所選計劃）：

請注意，使用應用嵌套循環的執行計劃使用“內連接”語義產生正確的結果，而不管GROUP BY子句是否存在。

在現實世界中，我們通常會有一個索引來支持在apply的內側進行查找，以鼓勵 SQL Server 自然地選擇此選項，例如：

CREATE INDEX i ON #MyTable (Col_A, Col_B);

db<>小提琴展示

引用自：https://dba.stackexchange.com/questions/239865