Mysql中的join、cross join、inner join是等效的

今天在Mysql官网（参考博客1）看到一段话：

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:

(T1, ...) RIGHT JOIN (T2, ...) ON P(T1, ..., T2, ...)

Becomes this equivalent left join:

(T2, ...) LEFT JOIN (T1, ...) ON P(T1, ..., T2, ...)

All inner join expressions of the form T1 INNER JOIN T2 ON P(T1,T2) are replaced by the list T1,T2,  and 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).

这段话表明，在Mysql的执行引擎对sql的解析阶段，都会将right join转换为left join；而对于inner join，则会进行如下转换：

FROM (T1, ...) INNER JOIN (T2, ...) ON P(T1, ..., T2, ...)

转换为：

FROM (T1, ..., T2, ...) WHERE P(T1, ..., T2, ...)

其实对于right join转换为left join是可以理解的，因为通过这样的转换，一方面可以使得底层的实现变得统一，另一方面其实也是受限于Mysql只实现了nested-join loop（NLJ）这一种算法（其他所谓的BNL、BKA等算法本质上还是NLJ），后面再细讲（详见参考博客3和4）。但对于将inner join所进行的转换我就表示不理解：因为这个转换相当于是将inner join转换为了cross join，而标准的SQL中，这两者肯定是不等价的。cross join是纯粹的笛卡尔积，连表后的记录行数比inner join要多。直到我看到了Mysql官网（参考博客2）上的另一段话：

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ONclause, CROSS JOIN is used otherwise.

这段话表明，在MySQL中，join、cross join和inner join这三者是等效的，而在标准的SQL查询中，这三者是不等效的。到这里，一切就能说得通了。

除此之外，我在Mysql官网上还看到一段话（参考博客2）：

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. 这段话说明了为什么Mysql要将right join转换为left join。因为Mysql只实现了nested-loop算法，该算法的核心就是外表驱动内表：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

由此可知，它必须要保证外表先被访问。有兴趣的可以进一步看参考博客4，介绍了三种表连接的算法。

最后，在Mysql官网上还看到一段话（参考博客1）：

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

SELECT * T1 LEFT JOIN T2 ON P1(T1,T2)
  WHERE P(T1,T2) AND R(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.

Instead, MySQL converts the query to a query with no outer join operation if the WHEREcondition 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.

也就是说，Mysql引擎在一些特殊情况下，会将left join转换为inner join。这里涉及到两个问题：1.为什么要做这样的转换？2.什么条件下才可以做转换？

其实官网对这两个问题都做了回答，不过对于第二个问题的回答方式可能不是那么容易理解。本文说说对这两个问题的理解：

首先，做转换的目的是为了提高查询效率。在上面的示例中，有可能where条件中的R(T2)可以极大地过滤不满足条件的记录，但由于nested loop算法的限制，只能先查T1，再用T1驱动T2。而如果在满足某些条件的情况下，我们将left join改写成inner join，那么mysql就可以自行决定是先查T1还是先查T2。在上面的示例中，如果我们将left join改写成inner join，由于where条件中的R(T2)可以极大地过滤不满足条件的语句，mysql先查T2，再查T1就会有较大的性能提升。当然，不是所有的left join都能转换为inner join，这就涉及到第2个问题。如果你深知left join和inner join的区别就很好理解第二个问题的答案（不知道两者区别的请自行百度）：

left join是以T1表为基础，让T2表来匹配，对于没有被匹配的T1的记录，其T2表中相应字段的值全为null。也就是说，left join连表的结果集包含了T1中的所有行记录。与之不同的是，inner join只返回T1表和T2表能匹配上的记录。也就是说，相比left join，inner join少返回了没有被T2匹配上的T1中的记录。那么，如果where中的查询条件能保证返回的结果中一定不包含不能被T2匹配的T1中的记录，那就可以保证left join的查询结果和inner join的查询结果是一样的，在这种情况下，就可以将left join转换为inner join。

我们再回过头来看官网中的例子：

T2.B IS NOT NULL
T2.B > 3
T2.C <= T1.C
T2.B < 2 OR T2.C > 1

如果上面的R(T2)是上面的任意一条，就能保证inner join的结果集中一定没有不能被T2匹配的T1中的记录。以T2.B > 3为例，对于不能被T2匹配的T1中的结果集，其T2中的所有字段都是null，显然不满足T2.B > 3。

相反，以下R(T2)显然不能满足条件，原因请自行分析：

T2.B IS NULL
T1.B < 3 OR T2.B IS NOT NULL
T1.B < 3 OR T2.B > 3

参考博客：

1.https://dev.mysql.com/doc/refman/5.6/en/outer-join-simplification.html mysql官网

2.https://dev.mysql.com/doc/refman/5.6/en/join.html mysql官网

3.http://blog.sina.com.cn/s/blog_aed82f6f0102x8al.html mysql join性能原理

4.https://www.cnblogs.com/xqzt/p/4469673.html 表连接的三种方式详解 hash join、merge join、 nested loop