MySQL-5.7-8.2.1.9 Outer Join Simplification

Table expressions in the FROM clause of a query are simplified in many cases.

在很多情况下，查询的FROM子句中的表表达式都被简化了。

At the parser stage, queries with right outer join operations are converted to equivalent queries containing only left join operations. In the general case, the conversion is performed such that this right join:

在解析器阶段，具有右外连接操作的查询被转换为只包含左外连接操作的等价查询。在一般情况下，转换的执行是这样的:

All inner join expressions of the form T1 INNER JOIN T2 ON P(T1,T2) are replaced by the list T1,T2, P(T1,T2) being joined as a conjunct to the WHERE condition (or to the join condition of the embedding join, if there is any).

所有形式为T1 inner join T2 ON P(T1,T2)的内连接表达式都被列表T1,T2, P(T1,T2)替换，该列表作为WHERE条件的连接(或嵌入连接的连接条件，如果有的话)连接。

When the optimizer evaluates plans for outer join operations, it takes into consideration only plans where, for each such operation, the outer tables are accessed before the inner tables.

当优化器计算外部连接操作的计划时，它只考虑以下计划:对于每个这样的操作，外部表在内部表之前被访问。

The optimizer choices are limited because only such plans enable outer joins to be executed using the nested-loop algorithm.

 优化器的选择是有限的，因为只有这样的计划才允许使用嵌套循环算法执行外部连接。

Consider a query of this form, where R(T2) greatly narrows the number of matching rows from table T2:

考虑这种形式的查询，其中R(T2)极大地缩小了表T2中匹配的行数:

If the query is executed as written, the optimizer has no choice but to access the less-restricted table T1 before the more-restricted table T2, which may produce a very inefficient execution plan.

如果查询按照所写的方式执行，优化器别无选择，只能先访问受限较少的表T1，然后访问受限较多的表T2，这可能会产生非常低效的执行计划。

Instead, MySQL converts the query to a query with no outer join operation if the WHERE condition is null-rejected. (That is, it converts the outer join to an inner join.) A condition is said to be null-rejected for an outer join operation if it evaluates to FALSE or UNKNOWN for any NULL-complemented row generated for the operation.

相反，如果WHERE条件被null拒绝，MySQL会将查询转换为没有外部连接操作的查询。(也就是说，它将外部连接转换为内部连接。)对于外部连接操作，如果为该操作生成的任何空补行求值为FALSE或UNKNOWN，则该条件被称为null-rejected。

Thus, for this outer join:

因此，对于这个外连接:

Conditions such as these are null-rejected because they cannot be true for any NULL-complemented row (with T2 columns set to NULL):

像这样的条件被NULL拒绝，因为它们不能为任何空补行(T2列设置为NULL):

Conditions such as these are not null-rejected because they might be true for a NULL-complemented row:

这样的条件不会被null拒绝，因为它们可能对一个空补行为真:

The general rules for checking whether a condition is null-rejected for an outer join operation are simple:

对于外部连接操作，检查条件是否为null拒绝的一般规则很简单:

• It is of the form A IS NOT NULL, where A is an attribute of any of the inner tables

• 它的形式是A is NOT NULL，其中A是任何内部表的属性

• It is a predicate containing a reference to an inner table that evaluates to UNKNOWN when one of its arguments is NULL

• 它是一个谓词，包含对内部表的引用，当其中一个参数为NULL时，计算结果为UNKNOWN

• It is a conjunction containing a null-rejected condition as a conjunct

• 它是一个连接词，包含一个null拒绝条件作为连接词

• It is a disjunction of null-rejected conditions

• 它是null拒绝条件的分离

A condition can be null-rejected for one outer join operation in a query and not null-rejected for another. In this query, the WHERE condition is null-rejected for the second outer join operation but is not null-rejected for the first one:

对于查询中的一个外部连接操作，条件可以被null拒绝，而对于另一个外部连接操作，条件可以不被null拒绝。在这个查询中，对于第二个外部连接操作，WHERE条件被null拒绝，但对于第一个外部连接操作，WHERE条件没有被null拒绝:

If the WHERE condition is null-rejected for an outer join operation in a query, the outer join operation is replaced by an inner join operation.

如果查询中的外部连接操作的WHERE条件为null拒绝，则外部连接操作将被内部连接操作取代。

For example, in the preceding query, the second outer join is null-rejected and can be replaced by an inner join:

例如，在前面的查询中，第二个外部连接被null拒绝，可以被内部连接替换:

For the original query, the optimizer evaluates only plans compatible with the single table-access order T1,T2,T3. For the rewritten query, it additionally considers the access order T3,T1,T2.

对于原始查询，优化器只评估与单个表访问顺序T1、T2、T3兼容的计划。对于重写的查询，它额外考虑访问顺序T3、T1、T2。

A conversion of one outer join operation may trigger a conversion of another. Thus, the query:

一个外部连接操作的转换可能会触发另一个外部连接操作的转换。因此,查询:

Is first converted to the query:

首先转换为查询:

Which is equivalent to the query:

这相当于查询:

The remaining outer join operation can also be replaced by an inner join because the condition T3.B=T2.B is null-rejected. This results in a query with no outer joins at all: 

由于条件T3.B=T2，其余的外部连接操作也可以用内部连接替换。B是null-rejected。这将导致一个完全没有外部连接的查询:

Sometimes the optimizer succeeds in replacing an embedded outer join operation, but cannot convert the embedding outer join. The following query:

有时优化器可以成功地替换嵌入的外部连接操作，但不能转换嵌入的外部连接。以下查询:

That can be rewritten only to the form still containing the embedding outer join operation:

只能重写为仍然包含嵌入外连接操作的形式:

Any attempt to convert an embedded outer join operation in a query must take into account the join condition for the embedding outer join together with the WHERE condition. In this query, the WHERE condition is not null-rejected for the embedded outer join, but the join condition of the embedding outer join T2.A=T1.A AND T3.C=T1.C is null-rejected:

 任何转换查询中嵌入外部连接操作的尝试都必须考虑嵌入外部连接的连接条件和WHERE条件。在这个查询中，嵌入外部连接的WHERE条件不是被null拒绝的，而是嵌入外部连接的连接条件T2.A=T1。A AND T3.C=T1.C is null-rejected:

Consequently, the query can be converted to:

因此，查询可以转换为: