Prayer

Terry Purcell 是在澳大利亞堪培拉舉行的 2001 IDUG 亞太地區(qū)會議的“Best Overall Speaker Award”的獲獎?wù)撸麑⒃谶@篇文章（分兩個部分）的第一部分中，就如何在外部連接中編寫謂詞提供一些建議。

您在何處編寫謂詞？（第 1 部分）

閱讀第 2 部分

自 SQL 構(gòu)造在 DB2 for OS/390® V6 中修訂之后，如果我相信有一種 SQL 構(gòu)造已經(jīng)造成了最多的疑惑，那一定就是外連接。

V6 擴(kuò)展了在 ON 子句中編寫謂詞的能力，并引入了大量其它的優(yōu)化和查詢改寫方面的增強(qiáng)。雖然增強(qiáng)語法的確增加了外連接的潛在用法，但這也意味著需要去理解更多的內(nèi)容。而語法也與它在 UNIX®、Linux、Windows 和 OS/2® 平臺上的兄弟更加接近，使得在 DB2 系列中更容易保持 SQL 編碼的一致性。

這篇文章由兩個部分組成，我試圖在文章中為編寫外連接總結(jié)出一個指南以實現(xiàn)兩個目的：

• 最重要的目標(biāo)是獲得正確的結(jié)果。
• 其次，考慮用不同的方法編寫謂詞的性能含義。

 

第 1 部分是關(guān)于外連接的更簡單構(gòu)造，就在 ON 和 WHERE 子句中編寫謂詞的效果進(jìn)行簡單的比較。在第 2 部分，我會涉及更復(fù)雜的主題，如簡化外連接和嵌套外連接。

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

對于 z/OS® 和 OS/390 的用戶，表名將有所不同：

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

回頁首

從內(nèi)連接到外連接

• 內(nèi)連接
• 外連接的分類
• 左外連接
• 右外連接
• 全外連接

內(nèi)連接

對于內(nèi)連接（或簡單表連接），只在結(jié)果中包含根據(jù)連接謂詞所匹配的行。因此，沒有包含那些不匹配的行。

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

這個示例使用“顯式連接”語法，以此在兩個被連接表之間編寫關(guān)鍵字“INNER JOIN”（或者只是 JOIN）。連接謂詞被編寫在 ON 子句中。盡管對于內(nèi)連接，這并不是強(qiáng)制的語法，然而針對外連接卻是強(qiáng)制的，因此這也是保持一致性的非常好的編程習(xí)慣?？紤]采用此語法還有一些其它原因：

• 比起在 FROM 子句中用逗號簡單地分隔表，這樣更具描述性。這在查詢變得很長時，非常重要。
• 在每次連接后，它強(qiáng)制對（ON 子句中的）連接謂詞進(jìn)行編碼，這意味著您不太可能忘記編寫連接謂詞。
• 很容易確定哪個連接謂詞屬于哪張表。
• 如果必要，很容易能夠?qū)?nèi)連接轉(zhuǎn)換為外連接。

 

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

外連接表的分類

在探究外連接示例之前，重要的是首先要了解連接中是如何分類表的。

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

保留行表是：

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

 

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

替換 NULL 的表是：

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

 

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

在 FROM 子句中編寫表的順序?qū)τ谧笸膺B接、右外連接以及涉及兩張表以上的連接極端重要，因為當(dāng)連接中存在不匹配的行時，保留行表和替換 NULL 的表的表現(xiàn)不同。

左外連接

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

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

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

右外連接

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

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

全外連接

全外連接返回那些存在于右表但不存在于左表（DEPTNO = 'A00'）的行，加上那些存在于左表但不存在于右表的行（DEPTNO = 'E01'），還有內(nèi)連接的行。

這兩張表既替換 NULL，也保留行。然而，因為存在分別適用于替換 NULL 的表和保留行表的“查詢改寫”和“WHERE 子句謂詞求值”的規(guī)則，所以表被標(biāo)識為替換 NULL 的表。我會在隨后的示例中更多地描述這之間的差異。

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

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

回頁首

外連接謂詞的類型

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

在發(fā)布 DB2 for OS/390 V6 前，謂詞只能夠應(yīng)用于連接前或者完全連接后。V6 引入了“連接時”的謂詞和“分步連接后”的謂詞的概念。

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

連接時的謂詞是指那些在 ON 子句中編碼的謂詞。對于所有連接（除了全外連接），這些謂詞可被視為嵌套循環(huán)或者混合式連接的內(nèi)連接表上規(guī)則的、可索引的階段 1 或者階段 2 的謂詞（類似于連接前的謂詞）。對于全外連接，或者任何使用合并掃描連接的連接，這些謂詞在階段 2（此時從物理上進(jìn)行行的連接）中應(yīng)用。

分步連接后的謂詞可以在連接之間應(yīng)用。這些可以在連接 － 此時，WHERE 子句謂詞的所有列變得可用（簡單謂詞或用 OR 分隔的復(fù)雜謂詞）－ 后，在任何后續(xù)連接之前應(yīng)用。

完全連接后的謂詞依賴于在應(yīng)用它們之前發(fā)生的所有連接。

連接前的謂詞

在 V6 DB2 for OS/390 之前，DB2 只有有限的能力在連接前為應(yīng)用下推 WHERE 子句中的謂詞。因此，為了確保 WHERE 子句中的謂詞在連接前被應(yīng)用，您必須把謂詞編寫在嵌套表表達(dá)式中。這不僅增加了實現(xiàn)可接受性能的復(fù)雜性，而且嵌套表表達(dá)式也要求在連接前具體化結(jié)果方面的開銷。

從 V6 開始，DB2 能夠把嵌套表表達(dá)式合并為單個查詢塊，因而避免了任何不必要的具體化。DB2 依據(jù) Administration Guide或者 Application Programming and SQL Guide中列出的具體化標(biāo)準(zhǔn)規(guī)則，強(qiáng)制地合并任何嵌套表表達(dá)式。

與用嵌套表表達(dá)式編寫謂詞不同的是，現(xiàn)在可以在 WHERE 子句中編寫謂詞，如 圖 7所示。

在 WHERE 子句中編寫連接前的謂詞的規(guī)則是它們必須僅應(yīng)用于保留行表；或者更確切地說，不能在替換 NULL 的表中應(yīng)用 WHERE 子句。這意味著您不再需要在嵌套表表達(dá)式中編寫謂詞。

對于全外連接，沒有一張表可以被僅僅標(biāo)識為保留行表，當(dāng)然，兩張表都是替換 NULL 的表。對于替換 NULL 的表，在 WHERE 子句中編寫謂詞的風(fēng)險是：它們或者會在連接后被全部應(yīng)用，或者會導(dǎo)致外連接過于簡單化（這些內(nèi)容我會在第 2 部分中討論）。為了在連接前應(yīng)用謂詞，您必須在嵌套表表達(dá)式中編寫它們，如 圖 8所示。

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

連接時的謂詞

在 ON 子句上編寫連接謂詞對于外連接是強(qiáng)制性的。在 DB2 for OS/390 V6 和隨后的版本中，您也可以在 ON 子句中編寫表達(dá)式或“列與文字”的比較關(guān)系（例如 DEPTNO = 'D01' ）。然而，ON 子句中的編碼表達(dá)式可以產(chǎn)生和 WHERE 子句中同樣編碼表達(dá)式截然不同的結(jié)果。

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

圖 9顯示了在左外連接 ON 子句中編寫表達(dá)式的結(jié)果。這不是大多數(shù)人編寫此類查詢時預(yù)期的結(jié)果。

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

當(dāng) DB2 訪問第一張表，并確定 ON 子句會失敗時（例如當(dāng) P.DEPTNO <> 'D01' 時），那么為了提高性能，DB2 立刻為替換 NULL 的表中的列替換 NULL，而不再嘗試連接行。

現(xiàn)在讓我們討論一下針對全外連接連接時的謂詞的情況。全外連接 ON 子句的規(guī)則和左外連接、右外連接一樣：在 ON 子句中的謂詞不限制返回的生成行數(shù)量，只限制哪些行可以被連接。

對于 圖 10 中的示例，因為沒有 WHERE 子句謂詞來限制結(jié)果，并且因為兩張全連接的表都保留行，所以返回所有左表和右表中的行。但是 ON 子句規(guī)定只有當(dāng) P.DEPTNO = D.DEPTNO AND P.DEPTNO = 'D01' 時才發(fā)生連接。當(dāng) ON 子句為假（也就是當(dāng) P.DEPTNO <> 'D01' ）時，那么將與正在保留行表相反方向的表中選擇的列的行替換為 NULL。

注釋：這個語法只能是非 OS/390 的，因為 OS/390 不允許在全連接的 ON 子句存在表達(dá)式。

為了促使非 OS/390 與 OS/390 DB2 語法相符合，我們必須首先派生表達(dá)式作為嵌套表表達(dá)式中的一列，然后再執(zhí)行連接。通過首先在 圖 11 中派生 DEPT2 列為 'D01'，只有當(dāng) P.DEPTNO = 'D01' 時，ON 子句才會有效地形成一個連接。

連接后的謂詞

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

WHERE 子句中第一個復(fù)合的謂詞只引用表 D 和 E（ D.MGRNO = E.EMPNO OR E.EMPNO IS NULL ）。因此，如果優(yōu)化器選擇的連接順序模仿 SQL 編碼的話，那么 DB2 能夠在連接 D 和 E 之后以及在連接 P 之前應(yīng)用 WHERE 子句中的謂詞。然而，WHERE 子句中第二個復(fù)合謂詞引用表 D 和 P（ D.MGRNO = P.RESPEMP OR P.RESPEMP IS NULL ）。這些是連接序列中的第一和第三張表。直到第三張表，也就是連接序列中的最后一張表被連接后，才能夠應(yīng)用謂詞。因此這稱為完全連接后的謂詞。

如果表連接的序列發(fā)生改變，分步連接后的謂詞很可能被轉(zhuǎn)換為完全連接后的謂詞；只要 DB2 OS/390 優(yōu)化器能夠根據(jù)最低成本存取路徑重新安排表連接序列，這是完全可能的。只要 DB2 能夠在連接之間盡早地應(yīng)用謂詞來限制后續(xù)連接所需要的行，那么您也應(yīng)該嘗試編寫謂詞使得 DB2 能夠盡早在連接序列中應(yīng)用謂詞。

回頁首

結(jié)束語

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

• FROM 子句中表的順序以及對內(nèi)連接和外連接的影響
• 這些連接類型之間的差別
• 不同的謂詞類型

 

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

• 連接前的謂詞
• 分步連接后的謂詞或者完全連接后的謂詞

 

如果這些謂詞當(dāng)前是在嵌套表表達(dá)式中編碼的，那么您現(xiàn)在可以在 WHERE 子句中寫上這些謂詞。連接前的謂詞是效率最高的謂詞，因為它們在連接前限制了行的數(shù)量。分步連接后的謂詞也限制了后續(xù)連接的行的數(shù)量。因為過濾完全發(fā)生在所有連接之后，所以完全連接后的謂詞是其中效率最低的。

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

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

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

關(guān)于作者

		Terry Purcell 是一名業(yè)界公認(rèn)的在 DB2 SQL 方面的權(quán)威。他出席過許多有關(guān)復(fù)雜的 SQL 的會議，并發(fā)表過許多有關(guān) SQL 性能的文章。他是 DB2 Performance Journal 的定期作者。Terry 從事 DB2 數(shù)據(jù)庫管理和應(yīng)用程序開發(fā)已經(jīng)十多年了。他還是 IBM 認(rèn)證的 DB2 V7 Database Administration for OS/390 解決方案專家，并且是 IBM DB2 金牌顧問（Gold Consultants）計劃的成員。