|
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 子句谓词时会发生什么情况。
我希望这篇文章能够让您对外连接有比较深刻的了解，也为您解决在何处编写外连接谓词问题时，提供一些线索。 |