• inner join ：内连接（等值连接）
• left join ：左连接
• right join ：右连接
• cross join : 交叉连接

ex：假设有如下两张表

inner join 和 cross join

下列语句输出结果相同

select 
	user.name,
  user.age,
  depart.department
from
	user 
inner join
	depart
on
	user.name = depart.name


select 
	user.name,
  user.age,
  depart.department
from
	user 
cross join
	depart
on
	user.name = depart.name

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

• 如果省略掉 inner/cross 得到的结果是一样的
• 如果不加on条件，得到的结果是两张标的”笛卡尔集“，如下

left join

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

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

right join

right join取得右表所有记录，若左表中不存在匹配记录，则用null代替

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

针对join语句如何建立索引

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

以以下 SQL 为例

select user.name,user.age,depart.department
from user
left join depart
on user.name = depart.name

在以上SQL中，查询的主表是user，然后根据on条件去depart表中查询。如果存在相同的name，那么进行数据组合，反之用null代替。

在该例中，驱动表就是 user，是主动发起查询的表，被驱动表是 depart，是根据on条件去查询的表。
MySQL优化器，对于驱动表会有一个选择的过程，使用关键字 straight_join 可以固定驱动表。如下

select user.name,user.age,depart.department
from user
straight_join depart
on user.name = depart.name

straight_join 使用效果和 left join相同，但是其可以帮助固定驱动表。

如果不使用 straight_join 关键字，则会按照MySQL优化器来选择驱动表，即：

• 若指定了连接条件，选择满足查询条件的记录行数少的表来作为驱动表
• 若没有指定连接条件，则扫描行数少的表作为驱动表

即，MySQL优化器选择驱动器是以小表驱动大表来决定

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

Index Nested-Loop Join

如果在被驱动表 B 的字段 name 上建立索引

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

Simple Nested-Loop Join

如果被驱动表B的字段name上没有索引
那么该sql通过MySQL优化器进行优化之后，驱动表A数据行数少，作为驱动表。则其查询顺序为

• 首先读取表A中的所有数据，查询到驱动表中的数据然后放入到 join buffer 中去
  • 数据放到join buffer 中的条件：type = ALL/INDEX ，其他情况查询效率更高，不需要把数据放到join buffer中去
• 然后从读取到的A表数据中拿到 name 数据项，然后全表扫描 b 表得到匹配项——简称 SNL
• 最后输出结果——其时间复杂度是$10\ast 10000=10^6$

Block Nested-Loop Join

BNL

• 读取A表中的数据放到join buffer中
• 扫描B表,把B表中的每一行数据取出来,跟join buffer中的数据对比,满足on条件的,作为结果集返回.
• 在内存中进行该操作,相对快过SNL
• 如果一次性查询出的数据大于 join_buffer_size 那么将会分块join