MySQL-5.7-Nested Join Optimization(嵌套连接优化)

The syntax for expressing joins permits nested joins. The following discussion refers to the join syntax described in Section 13.2.9.2, “JOIN Clause”.

表示连接的语法允许嵌套连接。

The syntax of table_factor is extended in comparison with the SQL Standard.

与SQL Standard相比，table_factor的语法得到了扩展。

The latter accepts only table_reference, not a list of them inside a pair of parentheses.

后者只接受table_reference，而不是圆括号内的列表。

This is a conservative extension if we consider each comma in a list of table_reference items as equivalent to an inner join. For example:

如果我们认为table_reference项列表中的每个逗号等同于一个内部连接，那么这就是一个保守的扩展。例如:

In MySQL, CROSS JOIN is syntactically equivalent to INNER JOIN; they can replace each other. In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause; CROSS JOIN is used otherwise.

在MySQL中，CROSS JOIN在语法上相当于INNER JOIN;它们可以相互替代。在标准SQL中，它们是不相等的。INNER JOIN与ON子句一起使用;否则将使用CROSS JOIN。

In general, parentheses can be ignored in join expressions containing only inner join operations. Consider this join expression:

通常，在只包含内部连接操作的连接表达式中，括号可以被忽略。考虑这个连接表达式:

After removing parentheses and grouping operations to the left, that join expression transforms into this expression:

在移除括号和左侧的分组操作后，连接表达式转换为下面的表达式:

Yet, the two expressions are not equivalent. To see this, suppose that the tables t1, t2, and t3 have the following state:

 然而，这两个表达式是不相等的。为了看到这一点，假设表t1、t2和t3的状态如下:

• Table t1 contains rows (1), (2)

• Table t2 contains row (1,101)

• Table t3 contains row (101)

In this case, the first expression returns a result set including the rows (1,1,101,101), (2,NULL,NULL,NULL), whereas the second expression returns the rows (1,1,101,101), (2,NULL,NULL,101):

在这种情况下，第一个表达式返回一个结果集，包括行(1,1,101,101)，(2,NULL,NULL,NULL)，而第二个表达式返回行(1,1,101,101)，(2,NULL,NULL,101):

In the following example, an outer join operation is used together with an inner join operation:

在下面的示例中，外部连接操作与内部连接操作一起使用:

That expression cannot be transformed into the following expression:

该表达式不能转换为以下表达式:

For the given table states, the two expressions return different sets of rows:

对于给定的表状态，这两个表达式返回不同的行集:

 

 

Therefore, if we omit parentheses in a join expression with outer join operators, we might change the result set for the original expression.

 因此，如果省略带有外部连接操作符的连接表达式中的括号，则可能会改变原始表达式的结果集。

 

未完