Terry Purcell 谈外连接（第二部分）

引言
这是我的专题中的第二部分，在该专题中我想让您更加容易地理解和使用 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 表，而它们之间并不互相依赖。