在系列文章（两篇）的这一篇（第二篇）中，Terry Purcell（DB2 金牌顾问）提供更多的建议，这些建议关于在外连接中编写谓词代码（包括在替换 NULL 的表上编写谓词代码）来确保正确的结果。

引言
这是我的专题中的第二部分，在该专题中我想让您更加容易地理解和使用 SQL 语言强大的外连接功能。

第一部分提供了内连接和外连接之间简单的比较，并且也介绍了在外连接操作中用来说明表的新术语。最终，我论述了不同的谓词类型以及在哪个阶段 DB2® 可以应用它们。

在这个部分，我将提供在替换 NULL（NULL-supplying）的表上编写谓词的背景、DB2 可以如何简化查询来改善性能以及要确保获得期望的结果所能采取的步骤。

左外连接和右外连接替换 NULL 的表谓词

• 外连接简化
• 保留 NULL
• 连接前（before-join）谓词

外连接简化
回顾我在 第一部分中介绍的一些术语：

• 保留行（preserved row）表是为连接操作中不匹配的行保留行的表。
• 替换 NULL的表是为连接中不匹配的行提供 NULL 行的表。

然而，应用于保留行表的 WHERE 子句谓词的最重要的属性是 DB2 可以在连接之前或之后应用谓词；应用于替换 NULL 的表的 WHERE 子句谓词的效果却非常不同，因为如果该谓词抵消了由外连接引入的 NULL，那么它使 DB2 简化连接。

要说明我说的这些是什么意思，请参见 图 13，该图展示了引用替换 NULL 的表的 WHERE 子句谓词的示例。

对一个要限定的行，WHERE 子句谓词的计算值必须为 TRUE。如果在连接中出现不匹配的行，那么来自替换 NULL 的表的列是 NULL。当 DB2 将 WHERE 子句谓词 D.DEPTNAME NOT LIKE "%CENTER%" 与 NULL 进行比较时，其结果既不是 TRUE 也不是 FALSE，而是 UNKNOWN。因为该行的计算值不是 TRUE，所以不返回该行。因而，左外连接（left outer join）提供的 NULL 被 WHERE 子句谓词抵消了。这使 DB2 确定左外连接是不必要的，并使得查询被重写为一个内连接，该内连接可能是或者可能不是您在编写该查询时想要的。

DB2 将左外连接重写为内连接的好处是可以改善性能。应用到表 D 的谓词现在可以应用在连接之前（而不是之后），因为该谓词现在引用一个不替换 NULL 的表。保留行和替换 NULL 对于内连接是不相关的术语；假定在连接中将不返回不匹配行，即，所有表都是不替换 NULL 的。

保留 NULL
如果该外连接简化没有产生您想要的结果 － 即，您要求返回 NULL（或可选）行 － 那么使用 OR D.DEPTNAME IS NULL 来保留在答案集中的 NULL。

在 图 14中展示了这样的一个示例。

DB2 必须将 WHERE 子句谓词应用在连接之后，因为直到连接之后才知道行是匹配的（因而应用谓词的第一部分 － D.DEPTNAME NOT LIKE "%CENTER%" ）还是不匹配的（因而应用谓词的第二部分 － OR D.DEPTNAME IS NULL ）。

连接前谓词
如果您选择编写 WHERE 子句谓词，它被 DB2 在连接 之前应用到替换 NULL 的表上，那么会发生什么？

如果您这样做，那么没有 WHERE 子句谓词来限制保留行表上或最终结果中的行。您仅限制了来自替换 NULL 的表的行。 图 15展示了这样的结果。

图 16展示如果您将连接前谓词重新编写为一个 ON 子句谓词，那么返回相同的结果。

是在连接前还是连接过程中过滤来自替换 NULL 的表的行只是一个性能问题，DB2 根据所使用的连接方法来决定采用这两种方法中的哪一种。要产生正确的结果，两种方法都是有效的。因为行在连接中不匹配，所以在连接前除去该行不会影响输出。

DB2 可以（从 V6 开始）合并任何不必要的嵌套表表达式，例如替换 NULL 的表的嵌套表表达式（ 图 15）被重写为单个查询块并被应用为连接中或连接前谓词（ 图 16）。

全外连接替换 NULL 的表谓词

• 外连接简化
• 保留 NULL
• 连接前谓词

外连接简化
适用于左外连接和右外连接（right outer join）的外连接简化的规则对全连接也是有效的。应用于替换 NULL 的表并使 NULL 被抵消的 WHERE 子句谓词使 DB2 简化连接。因为两个表都提供 NULL，所以不管该子句应用于哪个表，它都可以抵消 NULL。

图 17展示了 WHERE 子句谓词的一个示例，该 WHERE 子句谓词应用于全外连接（full outer join），它使 DB2 简化连接。

如果不使用连接简化，该谓词应用为完全连接后的（totally-after-join）谓词。假定优化器可以确定该谓词抵消 NULL，那么它可以将查询重写为左外连接。根据选择的表连接顺序，全外连接可以重写为左或右外连接。

将查询重写为左外连接意味着 WHERE 谓词现在可以应用为连接前谓词，使得连接的行更少了。如果 WHERE 子句谓词抵消了来自两个表的 NULL，那么简化使查询被重写为一个内连接。将该查询作为内连接执行允许两个表上的谓词在连接前被应用。

可以通过 explain（计划表）输出的 JOIN_TYPE 列来标识连接简化。值“l”表明该连接已经被简化为左外连接（因为没有运行时右外连接），而“空白”指示该连接简化为内连接。

保留 NULL
如果您要求在结果集中返回来自两个表的 NULL，那么请更改 WHERE 子句谓词以确保这些 NULL 不被除去。

图 18展示了 WHERE 子句谓词的一个示例，该 WHERE 子句谓词确实保留了 NULL。COALESCE 返回列表中第一个非 NULL 的值。从而该 WHERE 比较始终以一个实际的值为参照（除非该列定义为允许 NULL）。

在 图 18中展示的示例中，WHERE 子句谓词完全地在连接后应用，因为该 WHERE 谓词依赖于来自两个表的列。

还有更好的选择吗？

连接前谓词
通过编写查询来将这些谓词应用在连接前，您可以获得更好的性能（如 图 19所示）。

当编写多个外连接时要小心

• 丢失的行
• 查找丢失的行

丢失的行
在 第一部分中，我提到当编写任何 SQL 语句时，主要着重于得到正确的结果。当有多个连接涉及到外连接时，很容易就“意外地”丢失基于连接谓词的来源的行。

当然，我已经说明对于不匹配的行，来自以 NULL 替换的表的列将是 NULL。如果来自以 NULL 替换的表的列在后继连接中被引用为连接谓词，那么 NULL 将永远不会满足等式，从而将不会进行更多的匹配。如果不询问数据，可能无法指出丢失了这些行，因为外连接简化可能不是 DB2 所必需的。外连接简化至少在计划表中是可以被识别的（基于 JOIN_TYPE 列）。

图 20展示一个在后继连接中使用的以 NULL 替换的列的示例。

step 1 应用连接前谓词。step 2 执行左外连接。在此示例中 department 和 employee 表的左外连接（step 2）不产生匹配行。从而，当执行 step 3 时（到 project 表的后继左外连接），以前连接的连接谓词的值是 NULL，因为它来自以 NULL 替换的表。然而，该行仍然被保留着，因为它是左外连接。而对于内连接，将不保留该行。

查找丢失的行
如果您确保后继的连接谓词始终引用来自保留行表的列，真实的值将对于后续的连接可用。确保在连接中返回正确的行是极其重要的一点。

图 21展示了一个示例，其中第二个连接引用来自保留行表的连接谓词。

在此示例中，我改正了前一个示例（ 图 20）的错误。step 2（第一个左外连接）产生的数据包含来自保留行表的实际值“D01”。该值在 step 3（后继左外连接）中被用来与 project 表作比较，而不是 NULL。

此查询的连接顺序上的相关性要求首先访问 department 表。DB2 for z/OS™ 优化器（从 V6 开始）能够确定基于最低成本的剩余连接顺序，而不是编码顺序。突出显示为 step 2 和 step 3 的每个连接仅依赖于 department 表，而它们之间并不互相依赖。

对于全外连接，要指定来自保留行表（或不替换 NULL 的表）的连接列更加困难，因为两个表都能提供 NULL。您必须始终指定 COALESCE 子句来确保保留值始终由该连接指定。例如：

            ON P.DEPTNO =
            COALESCE(D.DEPTNO, E.WORKDEPT)
            

嵌套外连接

• 外连接简化
• 保留 NULL

外连接简化
我已经描述了 WHERE 子句使 DB2 简化外连接的情况。但是还有一种情况，在这种情况中 ON 子句可能引起这种简化的发生，这种情况发生在一个外连接嵌套在另一个外连接中时。DB2 执行的这种简化类型仅在 DB2 Application Programming and SQL Guide中作了简要说明。

嵌套连接似乎违反以下编写 ON 子句的规则：ON 子句必须紧跟在与它相关的连接之后。这种情况如 图 22所示。

在 图 22中，表 D“左连接”到表 P 和 E 的全外连接的结果。从而，ON 子句规定首先执行表 P 和 E 的连接。如果您在关键的地方加上圆括号并缩进该查询以增加可读性，那么这就变得更有逻辑了，例如：

            SELECT D.DEPTNO, D.DEPTNAME, P.DEPTNO, P.PROJNAME, E.EMPNO
            

            FROM  DEPARTMENT D LEFT OUTER JOIN
            

                (PROJECT P FULL OUTER JOIN EMPLOYEE EM
            

                 ON     P.DEPTNO = E.WORKDEPT)
            

            ON   D.DEPTNO = P.DEPTNO
            

            ORDER BY D.DEPTNO
            

加上圆括号后更容易看出首先执行全外连接，而且产生的表是左外连接的右边的表。

在全外连接（step 1）以后，结果包含左边或右边表为任何不匹配的行提供的 NULL。然后，Department 表“左连接”到该结果集（step 2）。对于 step 2，在“D”表中没有和提供的 NULL 相等的行，因此不连接这些行。其结果是 NULL 被最终的 ON 子句抵消。因此，DB2 认识到没有必要一开始就进行全连接编码。

前面示例的全外连接可以由 DB2 优化器重写为左外连接（在 图 23中）。

现在没有以 NULL 替换的行被 ON 子句抵消。

保留 NULL
要避免 DB2 执行这样的简化，（或更重要的是）保持 图 22的全外连接引入的所有行，请使用 COALESCE 来确保产生的连接列不是 NULL。

COALESCE 的存在确保后继的连接列不可能是 NULL。COALESCE 规定当列以 NULL 替换时就从保留行表中取值。例如，当左边是保留的表，那么右边是以 NULL 替换的表，反之亦然。

现在，因为 COALESCE 的缘故，在后继的连接列上没有引入任何 NULL，因而没有行被 ON 子句意外地抵消。

要点：正如我早先提到的那样，在全连接后的任何后继表连接必须在 ON 子句中包含 COALESCE 以确保以 NULL 替换的行不被连接抵消。

回顾我们在谓词方面学到的知识
从这篇文章的第 1 部分和第 2 部分中展示的示例，我们可以得出以下关于 DB2 如何计算谓词的结论：

WHERE 子句谓词：

• 应用到保留行表：
  • 用以下任一方式过滤行：
    • 连接前谓词
    • 分步连接后的（After-join-step）谓词
    • 完全连接后的谓词。
  • 在嵌套表表达式中的显式编码可以写入 WHERE 子句中。
• 应用到替换 NULL 的表：
  • 优化器简化连接，除非该谓词包含 OR colxIS NULL 或 COALESCE 来保留 NULL。
  • 在嵌套表表达式中的显式编码可以写入 ON 子句中。

ON 子句谓词：

• 在使用连接中（during-join）谓词时，仅过滤替换 NULL 的表的行。
• 不要过滤保留行表的行。

结束语
在我关于外连接的论述的这一部分中，我特别地着重于在以 NULL 替换的表上编写谓词。

最重要的是外连接简化，其中 SQL 编码器无意中编写了一个抵消以 NULL 替换的行的 WHERE 或 ON 子句谓词。如果用户显式地编写外连接，那么不可能故意地抵消这些行。当然，当编写任何 SQL 语句时，目标就是产生正确的结果。

有了这些新得到的知识，我希望您能编写外连接查询以您所期望的性能来产生您预期的结果。且不论其它的事情，我希望能让您了解 DB2 实现外连接的强大功能，并且给您一些在您编写查询时可以参考的东西。

关于作者 Terry Purcell 是 YL&A（Yevich, Lawson & Associates）的高级顾问,并且是业界公认的 DB2 SQL 权威。Terry 出席过许多有关 Complex SQL 的会议，并发表过许多有关 SQL 性能的文章，包括有关“Star Joins”的 IBM 白皮书。他是 DB2 Performance Journal 的定期作者，而且经常对 DB2 List Server 投稿。Terry 从事 DB2 的数据库管理和应用程序开发有十多年了。他还是 IBM 认证的 DB2 V7 Database Administration for OS/390® 的解决方案专家，并且是 IBM DB2 金牌顾问计划的成员。可以通过 Terry_Purcell@ylassoc.com和 Terry 联系。