MySQL之cross,inner,left和right详解

• inner join ：内部连接(等值连接)
• left join ：左连接
• right join ：右连接
• cross join : 交叉连接

ex：假设有以下两张表

下列语句的输出结果相同

select user.name,  user.age,  depart.departmentfromuser inner joindepartonuser.name = depart.nameselect user.name,  user.age,  depart.departmentfromuser cross joindepartonuser.name = depart.name

• 相同的输出结果 cross join 和 inner join 用法相同，取交集

• 如果省略掉 inner/cross 结果是一样的
• 如果没有on条件，结果是两个标的“笛卡尔集”，如下

left join获得左表的所有记录，如果右表没有匹配记录，则用null代替

• 必须使用 on 建立表链接的关键字

right join获得了右表的所有记录。如果左表中没有匹配记录，则用null代替

• 必须使用 on 关键字建立表连接

首先理解 ”驱动表“ 的概念

以以下 SQL 为例

select user.name,user.age,depart.departmentfrom userleft join departon user.name = depart.name

在上述SQL中，查询的主表是user，然后根据on条件在depart表中查询。如果有相同的name，则用null代替数据组合。

在这种情况下，驱动表是 user，是主动发起查询的表，被驱动表是 depart，是根据on条件查询的表。MySQL优化器将有一个选择驱动表的过程，使用关键字 straight_join 驱动表可以固定。如下。

select user.name,user.age,depart.departmentfrom userstraight_join departon user.name = depart.name

straight_join 使用效果和 left join是一样的，但它可以帮助固定驱动表。

如果不使用 straight_join 驱动表将根据MySQL优化器进行选择，即：

• 若指定连接条件，则选择符合查询条件的记录行数较少的表作为驱动表
• 如果没有指定的连接条件，扫描行数较少的表作为驱动表

也就是说，MySQL优化器的驱动器是由小表驱动的

假设 A 表中有 100 条记录， B 表中有 10000 条记录如下SQLselect * from A join B on A.name = B.name

若在被驱动表中 B 的字段 name 上建立索引

• 从A表中读取一行数据R
• 从数据行R中，将id字段取出到表B的name索引树上，找到相应的主键
• 然后根据主键返回表查询，取出表B中所有符合条件的行，然后与行R组成一行，作为结果集中的一部分
• 这种用途用于被驱动表 depart 的索引——Index Nested-Loop Join 简称 NLJ
• 时间复杂度 100 + 100 = 200行

如果驱动表B的字段name上没有索引，SQL通过MySQL优化器优化后，驱动表A的数据行数较少，作为驱动表。查询顺序为

• 首先读取表A中的所有数据，查询驱动表中的数据，然后放入表A中 join buffer 中去

• 将数据放入join buffer 中的条件：type = ALL/INDEX ，其他情况查询效率更高，不需要把将数据放入join buffer中去

• 然后从读取的A表数据中获取 name 数据项，然后全表扫描 b 表得到匹配项-简称匹配项- SNL
• 最终输出结果-时间复杂度

BNL

• 在join中读取A表中的数据 buffer中
• 扫描B表，取出B表中的每一行数据，跟随join 如果buffer中的数据比较符合on条件，则作为结果集返回.
• 该操作在内存中相对快于SNL
• 如果一次性查询的数据大于 join_buffer_size 然后将分块join