談外串連（第一部分）

自 SQL 構造在 DB2 for OS/390 V6 中修訂之後，如果我相信有一種 SQL 構造已經造成了最多的疑惑，那一定就是外串連。

V6 擴充了在 ON 子句中編寫謂詞的能力，並引入了大量其它的最佳化和查詢改寫方面的增強。雖然增強文法的確增加了外串連的潛在用法，但這也意味著需要去理解更多的內容。而文法也與它在 UNIX、Linux、Windows 和 OS/2 平台上的兄弟更加接近，使得在 DB2 系列中更容易保持 SQL 編碼的一致性。

這篇文章由兩個部分組成，我試圖在文章中為編寫外串連總結出一個指南以實現兩個目的：

• 最重要的目標是獲得正確的結果。
• 其次，考慮用不同的方法編寫謂詞的效能含義。

第 1 部分是關於外串連的更簡單構造，就在 ON 和 WHERE 子句中編寫謂詞的效果進行簡單的比較。在第 2 部分，我會涉及更複雜的主題，如簡化外串連和嵌套外串連。

本文中的例子使用了取自 DB2 通用資料庫（UDB）（非 OS/390）樣本資料庫的摘錄。 圖 1 中的資料是一整張表的子集。為了滿足所有外串連中組合的需要，Project 表中含有 PROJNO = 'IF2000' 的行已被更新為設定 DEPTNO = 'E01' 。

對於 z/OS 和 OS/390 的使用者，表名將有所不同：

工作站上 DB2 表的名稱	OS/390 和 z/OS 版本的 DB2 表的名稱
EMPLOYEE	EMP
DEPARTMENT	DEPT
PROJECT	PROJ

從內串連到外串連

• 內串連
• 外串連的分類
• 左外串連
• 右外串連
• 全外串連

內串連
對於內串連（或簡單表串連），只在結果中包含根據串連謂詞所匹配的行。因此，沒有包含那些不匹配的行。

在 圖 2 中，當在 DEPTNO 列上串連 Project 和 Department 兩張表時，在 Project（左）表中的行 DEPTNO = 'E01' 因為沒有在 Department 表中找到匹配的行，所以它不在結果集中返回。同樣的，在 department（右）表中的行 DEPTNO = 'A00' 也未匹配並且不返回。

這個樣本使用“顯式串連”文法，以此在兩個被串連表之間編寫關鍵字“INNER JOIN”（或者只是 JOIN）。串連謂詞被編寫在 ON 子句中。儘管對於內串連，這並不是強制的文法，然而針對外串連卻是強制的，因此這也是保持一致性的非常好的編程習慣。考慮採用此文法還有一些其它原因：

• 比起在 FROM 子句中用逗號簡單地分隔表，這樣更具描述性。這在查詢變得很長時，非常重要。
• 在每次串連後，它強制對（ON 子句中的）串連謂詞進行編碼，這意味著您不太可能忘記編寫串連謂詞。
• 很容易確定哪個串連謂詞屬於哪張表。
• 如果必要，很容易能夠將內串連轉換為外串連。

總之，關於內串連，人們經常問我：“在 FROM 子句中，用什麼順序編寫表是否很重要？”假如是為了檢索到正確的結果，回答是“不重要。”假如是針對效能，回答是“一般來說，不重要。”DB2 最佳化器評估全部可能的串連排列(順序)，並在其中選擇效率最高的一個。然而，引用 DB2 UDB for OS/390 and z/OS Administration Guide的話來說：FROM 子句中表或者視圖的順序可以影響存取路徑。對於這句話，我的理解是，如果兩個（或更多）不同的串連順序所花費的成本相同，那麼決勝的關鍵可能是 FROM 子句中表的順序。

外串連表的分類
在探究外串連樣本之前，重要的是首先要瞭解串連中是如何分類表的。

外串連的 FROM 子句中的表可以被分類成保留行（preserved row）表或者替換 NULL（NULL-supplying）的表。保留行表是指那些在串連操作中沒有匹配的內容時，把行保留下來的表。因此，將返回保留行表中所有滿足 WHERE 子句要求的行，無論在串連中是否存在匹配的行。

保留行表是：

• 左外串連中左邊的表。
• 右外串連中右邊的表。
• 全外串連中全部的表。

當不存在匹配的行時，替換 NULL 的表替換 NULL。如果串連操作中不存在匹配，任何在 SELECT 列表或者隨後的 WHERE 或者 ON 子句中引用的替換 NULL 的表中的列都將包含 NULL。

替換 NULL 的表是：

• 左外串連中右邊的表
• 右外串連中左邊的表
• 全外串連中全部的表

在全外串連中，兩張表既可以保留行，也可以替換 NULL。這一點非常重要，因為有些規則適用於純粹的保留行表，但是如果該表也替換 NULL，則會變得不適用。

在 FROM 子句中編寫表的順序對於左外串連、右外串連以及涉及兩張表以上的串連極端重要，因為當串連中存在不匹配的行時，保留行表和替換 NULL 的表的表現不同。

左外串連
圖 3展示了一個簡單的左外串連。

左外串連返回那些存在於左表而右表中卻沒有的行（ DEPTNO = 'E01' ），加上內串連的行。那些來自保留行表的未匹配行會被保留，而那些來自替換 NULL 的表中的行會以 NULL 替換。也就是說，當行與右邊的表不匹配時（ DEPTNO = 'E01' ），將從 DEPARTMENT 表以 NULL 替換作為 DEPTNO 的值。

請注意，select 列表同時包含來自保留行表和替換 NULL 的表中的 DEPTNO。從輸出中，您可以看到，如果可能，選擇來自保留行表的列非常重要，否則，列的值可能不存在。

右外串連

右外串連返回那些存在於右表而左表中沒有的行（ DEPTNO = 'A00' ），加上內串連的行。那些來自保留行表的未匹配行會被保留，而那些來自替換 NULL 的表中的行會由 NULL 替換。

對於右外串連，右表會成為保留行表，而左表會成為替換 NULL 的表。OS/390 版和 z/OS 版的 DB2 的最佳化器通過簡單地顛倒 FROM 子句中表的順序，以及把關鍵字從 RIGHT（右）更改為 LEFT（左），來重寫全部的右外串連，使之成為左外串連。這個查詢改寫只有通過方案表中的 JOIN_TYPE 列的“L”值來查看。為此，您應該避免編寫右外串連，以防您在解釋方案表（plan table）中的存取路徑時發生混淆。

全外串連

全外串連返回那些存在於右表但不存在於左表（DEPTNO = 'A00'）的行，加上那些存在於左表但不存在於右表的行（DEPTNO = 'E01'），還有內串連的行。

這兩張表既替換 NULL，也保留行。然而，因為存在分別適用於替換 NULL 的表和保留行表的“查詢改寫”和“WHERE 子句謂詞求值”的規則，所以表被標識為替換 NULL 的表。我會在隨後的樣本中更多地描述這之間的差異。

在本樣本中，選擇了兩個串連的列以顯示對於未匹配的行，任意一張表都替換 NULL。

為了保證總是返回非 NULL，請按以下方式編寫 COALESCE、VALUE 或 IFNULL 子句（該子句返回第一個不是 NULL 的參數）： COALESCE（P.DEPTNO,D.DEPTNO）。

外串連謂詞的類型

• 串連前的謂詞（before-join predicates）
• 串連時的謂詞（during-join predicates）
• 串連後的謂詞 (after-join predicates)

在發布 DB2 for OS/390 V6 前，謂詞只能夠應用於串連前或者完全串連後。V6 引入了“串連時”的謂詞和“分步串連後”的謂詞的概念。

DB2 可以在串連前應用串連前的謂詞來限定串連到後續表的行數。這些“本地的（Local）”或者“表訪問（table access）”的謂詞被視為成對串連的外串連表上規則的、可索引的階段 1 或者階段 2 謂詞求值。成對串連是描述兩個或者更多表的每個串連步驟的術語。例如，串連來自表 1 和表 2 中的行，把結果串連到表 3。每個串連每次只串連來自兩個表中的行。

串連時的謂詞是指那些在 ON 子句中編碼的謂詞。對於所有串連（除了全外串連），這些謂詞可被視為嵌套迴圈或者混合式串連的內串連表上規則的、可索引的階段 1 或者階段 2 的謂詞（類似於串連前的謂詞）。對於全外串連，或者任何使用合并掃描串連的串連，這些謂詞在階段 2（此時從物理上進行行的串連）中應用。

分步串連後的謂詞可以在串連之間應用。這些可以在串連 － 此時，WHERE 子句謂詞的所有列變得可用（簡單謂詞或用 OR 分隔的複雜謂詞）－ 後，在任何後續串連之前應用。

完全串連後的謂詞依賴於在應用它們之前發生的所有串連。

串連前的謂詞
在 V6 DB2 for OS/390 之前，DB2 只有有限的能力在串連前為應用下推 WHERE 子句中的謂詞。因此，為了確保 WHERE 子句中的謂詞在串連前被應用，您必須把謂詞編寫在巢狀表格運算式中。這不僅增加了實現可接受效能的複雜性，而且巢狀表格運算式也要求在串連前具體化結果方面的開銷。

從 V6 開始，DB2 能夠把巢狀表格運算式合并為單個查詢塊，因而避免了任何不必要的具體化。DB2 依據 Administration Guide或者 Application Programming and SQL Guide中列出的具體化標準規則，強制地合并任何巢狀表格運算式。

與用巢狀表格運算式編寫謂詞不同的是，現在可以在 WHERE 子句中編寫謂詞，如 圖 7所示。

在 WHERE 子句中編寫串連前的謂詞的規則是它們必須僅應用於保留行表；或者更確切地說，不能在替換 NULL 的表中應用 WHERE 子句。這意味著您不再需要在巢狀表格運算式中編寫謂詞。

對於全外串連，沒有一張表可以被僅僅標識為保留行表，當然，兩張表都是替換 NULL 的表。對於替換 NULL 的表，在 WHERE 子句中編寫謂詞的風險是：它們或者會在串連後被全部應用，或者會導致外串連過於簡單化（這些內容我會在第 2 部分中討論）。為了在串連前應用謂詞，您必須在巢狀表格運算式中編寫它們，如 圖 8所示。

因為串連前的謂詞限制了可以串連的行的數量，所以它們是此處描述的最有效率的謂詞類型。如果您從一張有五百萬行的表開始，在應用 WHERE 語句後只返回一行，那麼很顯然，在串連這一行前應用謂詞會更有效率。另外一個效率低下的選擇是，串連五百萬行，然後應用謂詞以得到一行的結果。

串連時的謂詞
在 ON 子句上編寫串連謂詞對於外串連是強制性的。在 DB2 for OS/390 V6 和隨後的版本中，您也可以在 ON 子句中編寫運算式或“列與文字”的比較關係（例如 DEPTNO = 'D01' ）。然而，ON 子句中的編碼錶達式可以產生和 WHERE 子句中同樣編碼錶達式截然不同的結果。

這是因為 ON 子句中的謂詞或者串連時的謂詞沒有限制返回結果行數的緣故；它們只限制了哪些行可以被串連。只有 WHERE 子句的謂詞限制了真正檢索到的行數。

圖 9顯示了在左外串連 ON 子句中編寫運算式的結果。這不是大多數人編寫此類查詢時預期的結果。

在此樣本中，因為沒有 WHERE 子句的謂詞來限制結果，所以返回所有保留行表（左表）中的行。但是 ON 子句規定，只有在同時滿足 P.DEPTNO = D.DEPTNO 和 P.DEPTNO = 'D01' 兩個條件時才發生串連。當 ON 子句為 false（也就是 P.DEPTNO <> 'D01' ）時，那些從替換 NULL 的表選中的列對應的行上將換成 NULL。類似的，當 P.DEPTNO 是 'E01' 時，那麼 ON 子句的第 1 個元素就失敗了，來自左表的行將被保留，而來自右表的行將替換為 NULL。

當 DB2 訪問第一張表，並確定 ON 子句會失敗時（例如當 P.DEPTNO <> 'D01' 時），那麼為了提高效能，DB2 立刻為替換 NULL 的表中的列替換 NULL，而不再嘗試串連行。

現在讓我們討論一下針對全外串連串連時的謂詞的情況。全外串連 ON 子句的規則和左外串連、右外串連一樣：在 ON 子句中的謂詞不限制返回的產生行數量，只限制哪些行可以被串連。

對於 圖 10 中的樣本，因為沒有 WHERE 子句謂詞來限制結果，並且因為兩張全串連的表都保留行，所以返回所有左表和右表中的行。但是 ON 子句規定只有當 P.DEPTNO = D.DEPTNO AND P.DEPTNO = 'D01' 時才發生串連。當 ON 子句為假（也就是當 P.DEPTNO <> 'D01' ）時，那麼將與正在保留行表相反方向的表中選擇的列的行替換為 NULL。

注釋：這個文法只能是非 OS/390 的，因為 OS/390 不允許在全串連的 ON 子句存在運算式。

為了促使非 OS/390 與 OS/390 DB2 文法相符合，我們必須首先派生運算式作為巢狀表格運算式中的一列，然後再執行串連。通過首先在 圖 11 中派生 DEPT2 列為 'D01'，只有當 P.DEPTNO = 'D01' 時，ON 子句才會有效地形成一個串連。

串連後的謂詞
圖 12中包含帶有分步串連後的（after-join-step）和完全串連後的（totally-after-join ）謂詞的查詢。

WHERE 子句中第一個複合的謂詞只參考資料表 D 和 E（ D.MGRNO = E.EMPNO OR E.EMPNO IS NULL ）。因此，如果最佳化器選擇的串連順序模仿 SQL 編碼的話，那麼 DB2 能夠在串連 D 和 E 之後以及在串連 P 之前應用 WHERE 子句中的謂詞。然而，WHERE 子句中第二個複合謂詞參考資料表 D 和 P（ D.MGRNO = P.RESPEMP OR P.RESPEMP IS NULL ）。這些是串連序列中的第一和第三張表。直到第三張表，也就是串連序列中的最後一張表被串連後，才能夠應用謂詞。因此這稱為完全串連後的謂詞。

如果表串連的序列發生改變，分步串連後的謂詞很可能被轉換為完全串連後的謂詞；只要 DB2 OS/390 最佳化器能夠根據最低成本存取路徑重新安排表串連序列，這是完全可能的。只要 DB2 能夠在串連之間儘早地應用謂詞來限制後續串連所需要的行，那麼您也應該嘗試編寫謂詞使得 DB2 能夠儘早在串連序列中應用謂詞。

結束語
在本文中，我描述了幾個主題：

• FROM 子句中表的順序以及對內串連和外串連的影響
• 這些連線類型之間的差別
• 不同的謂詞類型

總的來說，應用到保留行表中的 WHERE 子句謂詞可以作為以下謂詞類型來過濾行：

• 串連前的謂詞
• 分步串連後的謂詞或者完全串連後的謂詞

如果這些謂詞當前是在巢狀表格運算式中編碼的，那麼您現在可以在 WHERE 子句中寫上這些謂詞。串連前的謂詞是效率最高的謂詞，因為它們在串連前限制了行的數量。分步串連後的謂詞也限制了後續串連的行的數量。因為過濾完全發生在所有串連之後，所以完全串連後的謂詞是其中效率最低的。

最令人吃驚的是 ON 子句中的謂詞，因為它們作為串連時的謂詞僅僅過濾替換 NULL 的表中的行。它們不像 WHERE 子句中的謂詞那樣，過濾保留行表中的行。

在這篇文章的第 2 部分，我將描述如果針對替換 NULL 的表編寫 WHERE 子句謂詞時會發生什麼情況。

我希望這篇文章能夠讓您對外串連有比較深刻的瞭解，也為您解決在何處編寫外串連謂詞問題時，提供一些線索。