前言

提起这几种表连接方式就让人头大，想当初还因为这个面试被刷了，长得挺像，用法挺像，可就是有点不一样，其实的它们的差异不是固定的，要在一个具体的环境下才能进行对比，比如在Mysql环境下,JOIN, INNER JOIN, CROSS JOIN 三者在语法上是等价的，也就是作用相同，但是在标准的SQL下却又存在差异。

选一个自己熟悉的环境对比一下，那就是Mysql数据库的表连接了，测试的多了渐渐的发现了一些规律和神坑，貌似一切表连接都是以内连接为基础，然后再此基础上进行变换可以得到一种新的连接，接下来就采用这种对比的逻辑，看看这些连接类型都有什么区别和联系。

测试环境

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

创建测试数据

1. 新建第一个测试表格a，包含id和name两列

createtable a(idint, namevarchar(64),primarykey(id));

2. 插入测试数据

insertinto avalues(1,'albert');insertinto avalues(2,'bella');insertinto avalues(3,'amy');insertinto avalues(4,'forier');

3. 新建第二个测试表格b，包含id和age两列

createtable b(idint, ageint,primarykey(id));

4. 插入测试数据

insertinto bvalues(1,18);insertinto bvalues(2,19);insertinto bvalues(3,25);insertinto bvalues(5,70);

5. 分别查看两表中的数据如下

mysql>select*from a;+----+--------+| id| name|+----+--------+|1| albert||2| bella||3| amy||4| forier|+----+--------+4rowsinset(0.04 sec)

mysql>select*from b;+----+-----+| id| age|+----+-----+|1|18||2|19||3|25||5|70|+----+-----+4rowsinset(0.05 sec)

对比测试

这篇对比文章可能和以往你看到的不太一样，对比的基础是内连接，其他的连接基本可以看做是在内连接的基础上加了一些条件和扩展得到的，所以首先我们需要先来看一下内连接。

内连接

内连接基础语法是a inner join b，不过其中的inner可以省略，也就是可以写成a join b，如果不添加条件就是a表中的每条记录分别与b表中的每条记录做匹配，形成笛卡尔积，查询结果如下：

mysql>select*from ainnerjoin b;+----+--------+----+-----+| id| name| id| age|+----+--------+----+-----+|1| albert|1|18||2| bella|1|18||3| amy|1|18||4| forier|1|18||1| albert|2|19||2| bella|2|19||3| amy|2|19||4| forier|2|19||1| albert|3|25||2| bella|3|25||3| amy|3|25||4| forier|3|25||1| albert|5|70||2| bella|5|70||3| amy|5|70||4| forier|5|70|+----+--------+----+-----+16rowsinset(0.03 sec)

mysql>select*from ajoin b;+----+--------+----+-----+| id| name| id| age|+----+--------+----+-----+|1| albert|1|18||2| bella|1|18||3| amy|1|18||4| forier|1|18||1| albert|2|19||2| bella|2|19||3| amy|2|19||4| forier|2|19||1| albert|3|25||2| bella|3|25||3| amy|3|25||4| forier|3|25||1| albert|5|70||2| bella|5|70||3| amy|5|70||4| forier|5|70|+----+--------+----+-----+16rowsinset(0.04 sec)

需要注意的是内连接的连接条件是可选择，如果不加就是笛卡尔积，如果想加的话可以选择on子句或者using子句，比如需要得到a表与b表中id一致的数据记录就可以使用如下on子句的写法：

mysql>select*from ajoin bon a.id= b.id;+----+--------+----+-----+| id| name| id| age|+----+--------+----+-----+|1| albert|1|18||2| bella|2|19||3| amy|3|25|+----+--------+----+-----+3rowsinset(0.04 sec)

同时对于上述例子中这个on子句中是被连接的两表的同时存在的字段时，可以使用using子句简化，写成如下查询，需要注意下结果集的变化，记录的条数与on子句相同，但是共有的id列被优化掉了一个，这也是on和using子句的区别，使用时根据需要选择：

mysql>select*from ajoin busing(id);+----+--------+-----+| id| name| age|+----+--------+-----+|1| albert|18||2| bella|19||3| amy|25|+----+--------+-----+3rowsinset(0.04 sec)

交叉连接

交叉连接基础语法是a cross join b，在Mysql的语法环境中，内连接与交叉连接完全一致，这一点可以通过下面几条查询与内连接的查询做对比得知：

mysql>select*from acrossjoin b;+----+--------+----+-----+| id| name| id| age|+----+--------+----+-----+|1| albert|1|18||2| bella|1|18||3| amy|1|18||4| forier|1|18||1| albert|2|19||2| bella|2|19||3| amy|2|19||4| forier|2|19||1| albert|3|25||2| bella|3|25||3| amy|3|25||4| forier|3|25||1| albert|5|70||2| bella|5|70||3| amy|5|70||4| forier|5|70|+----+--------+----+-----+16rowsinset(0.04 sec)

mysql>select*from acrossjoin bon a.id= b.id;+----+--------+----+-----+| id| name| id| age|+----+--------+----+-----+|1| albert|1|18||2| bella|2|19||3| amy|3|25|+----+--------+----+-----+3rowsinset(0.04 sec)

mysql>select*from acrossjoin busing(id);+----+--------+-----+| id| name| age|+----+--------+-----+|1| albert|18||2| bella|19||3| amy|25|+----+--------+-----+3rowsinset(0.04 sec)

外连接

在Mysql中外连接分为左外连接和右外连接，但不存在全外连接，这一点与Oracle有些不同，不过可以通过左外连接和右外连接合并出全外连接的结果集，需要注意的是外连接必须添加on子句或者using子句，否则会报语法错误，对于左、有外连接可以分别看一下：

左外连接

左外连接基础语法是a left outer join b，其中的outer可以省略，与内连接相比就是在与内连接相同条件下，在内连接的结果集中添加a表在b表中找不到匹配的记录，换句话说就是，结果集中会包含a表中的所有记录，如果b表中有匹配的记录就出现在结果集，否则使用NULL代替，也就是把a表看成根本，不可缺失记录，查询结果如下:

mysql>select*from aleftouterjoin bon a.id= b.id;+----+--------+------+------+| id| name| id| age|+----+--------+------+------+|1| albert|1|18||2| bella|2|19||3| amy|3|25||4| forier|NULL|NULL|+----+--------+------+------+4rowsinset(0.04 sec)

mysql>select*from aleftjoin bon a.id= b.id;+----+--------+------+------+| id| name| id| age|+----+--------+------+------+|1| albert|1|18||2| bella|2|19||3| amy|3|25||4| forier|NULL|NULL|+----+--------+------+------+4rowsinset(0.03 sec)

这个左外连接查询同样可以使用using子句来化简，并且也会将共有的字段省略一个：

mysql>select*from aleftjoin busing(id);+----+--------+------+| id| name| age|+----+--------+------+|1| albert|18||2| bella|19||3| amy|25||4| forier|NULL|+----+--------+------+4rowsinset(0.04 sec)

右外连接

右外连接基础语法是a right outer join b，其中的outer可以省略，与内连接相比就是在与内连接相同条件下，在内连接的结果集中添加b表在a表中找不到匹配的记录，换句话说就是，结果集中会包含b表中的所有记录，如果a表中有匹配的记录就出现在结果集，否则使用NULL代替，也就是把b表看成根本，不可缺失记录，作用与左外连接恰好相反，查询结果如下:

mysql>select*from arightouterjoin bon a.id= b.id;+------+--------+----+-----+| id| name| id| age|+------+--------+----+-----+|1| albert|1|18||2| bella|2|19||3| amy|3|25||NULL|NULL|5|70|+------+--------+----+-----+4rowsinset(0.03 sec)

mysql>select*from arightjoin bon a.id= b.id;+------+--------+----+-----+| id| name| id| age|+------+--------+----+-----+|1| albert|1|18||2| bella|2|19||3| amy|3|25||NULL|NULL|5|70|+------+--------+----+-----+4rowsinset(0.04 sec)

mysql>select*from arightjoin busing(id);+----+-----+--------+| id| age| name|+----+-----+--------+|1|18| albert||2|19| bella||3|25| amy||5|70|NULL|+----+-----+--------+4rowsinset(0.04 sec)

自然连接

自然连接从名字来看就是两个表很自然的就连接上了，这要求两个表需要有可以参照的数据，具体到表设计上就是要求两个表必须要有相同的列，需要注意的是自然连接不允许添加连接子句，否则会报语法错误。自然连接分为一般自然连接、左外连接和自然右外连接连接，还是以内连接为标准，看看自然连接有什么不同：

一般自然连接

一般自然连接基础语法是a natural join b，它不能加连接条件，使用两个表共有的字段id来“自然”地链接，同时会省略共有的字段，其作用相同于内连接使用using子句来查询，通过下面的对比，你会发现他们的作用是一样的。

mysql>select*from anaturaljoin b;+----+--------+-----+| id| name| age|+----+--------+-----+|1| albert|18||2| bella|19||3| amy|25|+----+--------+-----+3rowsinset(0.03 sec)

mysql>select*from ajoin busing(id);+----+--------+-----+| id| name| age|+----+--------+-----+|1| albert|18||2| bella|19||3| amy|25|+----+--------+-----+3rowsinset(0.04 sec)

自然左外连接

自然左外连接基础语法是a natural left outer join b，其中的outer可以省略，它也不能加连接条件，使用两个表共有的字段id来“自然”地链接，同时会省略共有的字段，其作用相同于内连接使用using子句同时包含a表中的所有记录，以a表作为根本，包含所有记录，并且显示b表中匹配记录，如没有与a表匹配的记录则以NULL代替，其实就是左外连接省略掉using子句：

mysql>select*from anaturalleftouterjoin b;+----+--------+------+| id| name| age|+----+--------+------+|1| albert|18||2| bella|19||3| amy|25||4| forier|NULL|+----+--------+------+4rowsinset(0.04 sec)

mysql>select*from anaturalleftjoin b;+----+--------+------+| id| name| age|+----+--------+------+|1| albert|18||2| bella|19||3| amy|25||4| forier|NULL|+----+--------+------+4rowsinset(0.03 sec)

mysql>select*from aleftjoin busing(id);+----+--------+------+| id| name| age|+----+--------+------+|1| albert|18||2| bella|19||3| amy|25||4| forier|NULL|+----+--------+------+4rowsinset(0.03 sec)

自然右外连接

自然左