|
Terry Purcell
顧問
2001 年 12 月
Terry Purcell 是在澳大利亞堪培拉舉行的 2001 IDUG 亞太地區會議的“Best Overall Speaker Award”的獲獎者，他將在這篇文章（分兩個部分）的第一部分中，就如何在外部連接中編寫謂詞提供一些建議。
您在何處編寫謂詞？（第 1 部分）
閱讀第 2 部分
自 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）。
外連接謂詞的類型
在發布 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 子句謂詞時會發生什么情況。
我希望這篇文章能夠讓您對外連接有比較深刻的了解，也為您解決在何處編寫外連接謂詞問題時，提供一些線索。 |