Prayer

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

您在何處編寫(xiě)謂詞？（第 1 部分）

閱讀第 2 部分

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

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

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

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

 

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

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

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

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

回頁(yè)首

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

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

內(nèi)連接

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

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

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

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

 

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

外連接表的分類

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

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

保留行表是：

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

 

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

替換 NULL 的表是：

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

 

在全外連接中，兩張表既可以保留行，也可以替換 NULL。這一點(diǎn)非常重要，因?yàn)橛行┮?guī)則適用于純粹的保留行表，但是如果該表也替換 NULL，則會(huì)變得不適用。

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

左外連接

圖 3展示了一個(gè)簡(jiǎn)單的左外連接。

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

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

右外連接

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

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

全外連接

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

這兩張表既替換 NULL，也保留行。然而，因?yàn)榇嬖诜謩e適用于替換 NULL 的表和保留行表的“查詢改寫(xiě)”和“WHERE 子句謂詞求值”的規(guī)則，所以表被標(biāo)識(shí)為替換 NULL 的表。我會(huì)在隨后的示例中更多地描述這之間的差異。

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

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

回頁(yè)首

外連接謂詞的類型

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

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

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

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

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

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

連接前的謂詞

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

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

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

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

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

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

連接時(shí)的謂詞

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

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

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

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

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

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

對(duì)于 圖 10 中的示例，因?yàn)闆](méi)有 WHERE 子句謂詞來(lái)限制結(jié)果，并且因?yàn)閮蓮埲B接的表都保留行，所以返回所有左表和右表中的行。但是 ON 子句規(guī)定只有當(dāng) P.DEPTNO = D.DEPTNO AND P.DEPTNO = 'D01' 時(shí)才發(fā)生連接。當(dāng) ON 子句為假（也就是當(dāng) P.DEPTNO <> 'D01' ）時(shí)，那么將與正在保留行表相反方向的表中選擇的列的行替換為 NULL。

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

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

連接后的謂詞

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

WHERE 子句中第一個(gè)復(fù)合的謂詞只引用表 D 和 E（ D.MGRNO = E.EMPNO OR E.EMPNO IS NULL ）。因此，如果優(yōu)化器選擇的連接順序模仿 SQL 編碼的話，那么 DB2 能夠在連接 D 和 E 之后以及在連接 P 之前應(yīng)用 WHERE 子句中的謂詞。然而，WHERE 子句中第二個(gè)復(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)用謂詞來(lái)限制后續(xù)連接所需要的行，那么您也應(yīng)該嘗試編寫(xiě)謂詞使得 DB2 能夠盡早在連接序列中應(yīng)用謂詞。

回頁(yè)首

結(jié)束語(yǔ)

在本文中，我描述了幾個(gè)主題：

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

 

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

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

 

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

最令人吃驚的是 ON 子句中的謂詞，因?yàn)樗鼈冏鳛檫B接時(shí)的謂詞僅僅過(guò)濾替換 NULL 的表中的行。它們不像 WHERE 子句中的謂詞那樣，過(guò)濾保留行表中的行。

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

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

關(guān)于作者

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