求MySQL 中JOIN 的详细用法

1. Nested-Loop Join 翻译过来就是嵌套循环连接，简称 NLJ。

这种是 MySQL 里最简单、最容易理解的表关联算法。

比如，拿语句 select * from p1 join p2 using(r1) 来说，

先从表 p1 里拿出来一条记录 ROW1，完了再用 ROW1 遍历表 p2 里的每一条记录，并且字段 r1 来做匹配是否相同，以便输出；再次循环刚才的过程，直到两表的记录数对比完成为止。

2. Block Nested-Loop Join ，块嵌套循环，简称 BNLJ

那 BNLJ 比 NLJ 来说，中间多了一块 BUFFER 来缓存外表的对应记录从而减少了外表的循环次数，也就减少了内表的匹配次数。还是那上面的例子来说，假设 join_buffer_size 刚好能容纳外表的对应 JOIN KEY 记录，那对表 p2 匹配次数就由 1000 次减少到 1 次，性能直接提升了 1000 倍。

3. 最近 MySQL 8.0.18 发布，终于推出了新的 JOIN 算法 — HASH JOIN。

MySQL 的 HASH JOIN 也是用了 JOIN BUFFER 来做缓存，但是和 BNLJ 不同的是，它在 JOIN BUFFER 中以外表为基础建立一张哈希表，内表通过哈希算法来跟哈希表进行匹配，hash join 也就是进一步减少内表的匹配次数。当然官方并没有说明详细的算法描述，以上仅代表个人臆想。那还是针对以上的 SQL，我们来看下执行计划。

join 经常用来做关联查询，可以把两张或者多张表用通过关联条件关联起来做数据查询

在使用join查询的时候要区分主表和附表，jion ...on .....and

on: 表之间的关联条件

and：对附表做筛选

内连接，两个关联的表都为主表，所以他们的做条件筛选的顺序是：先连接，后筛选。此时 join ...on ... and =join...on... where ....

左连接，这时候左边的表就是主表，所以，主表的数据会全部展示出来，右边的表为附表，此时on连接后在通过and进行筛选的条件对主表不起作用，只对附表起作用。先筛选再连接

右连接，右边的表为主表，左边表变成附表，如果on后面又and 做筛选条件的话，和left join一样 也是先筛选后连接。

内连接

在mysql中，

inner join...on

 join...on

逗号...where

cross join...on

是一样的含义。

但是在标准SQL中，它们并不等价。

外链接

外链接包括left [outer] join 和right [outer] join ，左连接和右连接

RIGHT JOIN的作用与LEFT JOIN的作用类似。要使代码可以在数据库内移植，建议您使用LEFT JOIN代替RIGHT JOIN

自然连接

NATURAL [LEFT] JOIN：这个句子的作用相当于INNER JOIN，或者是在USING子句中包含了联结的表中所有公共字段的Left JOIN（左联结）。

---