Mysql表连接：内连接、外连接、交叉连接、自然连接真的都不一样吗

前言

提起这几种表连接方式就让人头大，想当初还因为这个面试被刷了，长得挺像，用法挺像，可就是有点不一样，其实的它们的差异不是固定的，要在一个具体的环境下才能进行对比，比如在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 (c) 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两列

   create table a(id int, name varchar(64), primary key(id));

2. 插入测试数据

   insert into a values(1, 'albert');
   insert into a values(2, 'bella');
   insert into a values(3, 'amy');
   insert into a values(4, 'forier');

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

   create table b(id int, age int, primary key(id));

4. 插入测试数据

   insert into b values(1, 18);
   insert into b values(2, 19);
   insert into b values(3, 25);
   insert into b values(5, 70);

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

   mysql> select * from a;
   +----+--------+
   | id | name   |
   +----+--------+
   |  1 | albert |
   |  2 | bella  |
   |  3 | amy    |
   |  4 | forier |
   +----+--------+
   4 rows in set (0.04 sec)
   
   mysql> select * from b;
   +----+-----+
   | id | age |
   +----+-----+
   |  1 |  18 |
   |  2 |  19 |
   |  3 |  25 |
   |  5 |  70 |
   +----+-----+
   4 rows in set (0.05 sec)

对比测试

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

内连接

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

mysql> select * from a inner join 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 |
+----+--------+----+-----+
16 rows in set (0.03 sec)

mysql> select * from a join 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 |
+----+--------+----+-----+
16 rows in set (0.04 sec)

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

mysql> select * from a join b on a.id = b.id;
+----+--------+----+-----+
| id | name   | id | age |
+----+--------+----+-----+
|  1 | albert |  1 |  18 |
|  2 | bella  |  2 |  19 |
|  3 | amy    |  3 |  25 |
+----+--------+----+-----+
3 rows in set (0.04 sec)

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

mysql> select * from a join b using(id);
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | albert |  18 |
|  2 | bella  |  19 |
|  3 | amy    |  25 |
+----+--------+-----+
3 rows in set (0.04 sec)

交叉连接

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

mysql> select * from a cross join 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 |
+----+--------+----+-----+
16 rows in set (0.04 sec)

mysql> select * from a cross join b on a.id = b.id;
+----+--------+----+-----+
| id | name   | id | age |
+----+--------+----+-----+
|  1 | albert |  1 |  18 |
|  2 | bella  |  2 |  19 |
|  3 | amy    |  3 |  25 |
+----+--------+----+-----+
3 rows in set (0.04 sec)

mysql> select * from a cross join b using(id);
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | albert |  18 |
|  2 | bella  |  19 |
|  3 | amy    |  25 |
+----+--------+-----+
3 rows in set (0.04 sec)

外连接

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

左外连接

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

mysql> select * from a left outer join b on a.id = b.id;
+----+--------+------+------+
| id | name   | id   | age  |
+----+--------+------+------+
|  1 | albert |    1 |   18 |
|  2 | bella  |    2 |   19 |
|  3 | amy    |    3 |   25 |
|  4 | forier | NULL | NULL |
+----+--------+------+------+
4 rows in set (0.04 sec)

mysql> select * from a left join b on a.id = b.id;
+----+--------+------+------+
| id | name   | id   | age  |
+----+--------+------+------+
|  1 | albert |    1 |   18 |
|  2 | bella  |    2 |   19 |
|  3 | amy    |    3 |   25 |
|  4 | forier | NULL | NULL |
+----+--------+------+------+
4 rows in set (0.03 sec)

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

mysql> select * from a left join b using(id);
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | albert |   18 |
|  2 | bella  |   19 |
|  3 | amy    |   25 |
|  4 | forier | NULL |
+----+--------+------+
4 rows in set (0.04 sec)

右外连接

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

mysql> select * from a right outer join b on a.id = b.id;
+------+--------+----+-----+
| id   | name   | id | age |
+------+--------+----+-----+
|    1 | albert |  1 |  18 |
|    2 | bella  |  2 |  19 |
|    3 | amy    |  3 |  25 |
| NULL | NULL   |  5 |  70 |
+------+--------+----+-----+
4 rows in set (0.03 sec)

mysql> select * from a right join b on a.id = b.id;
+------+--------+----+-----+
| id   | name   | id | age |
+------+--------+----+-----+
|    1 | albert |  1 |  18 |
|    2 | bella  |  2 |  19 |
|    3 | amy    |  3 |  25 |
| NULL | NULL   |  5 |  70 |
+------+--------+----+-----+
4 rows in set (0.04 sec)

mysql> select * from a right join b using(id);
+----+-----+--------+
| id | age | name   |
+----+-----+--------+
|  1 |  18 | albert |
|  2 |  19 | bella  |
|  3 |  25 | amy    |
|  5 |  70 | NULL   |
+----+-----+--------+
4 rows in set (0.04 sec)

自然连接

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

一般自然连接

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

mysql> select * from a natural join b;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | albert |  18 |
|  2 | bella  |  19 |
|  3 | amy    |  25 |
+----+--------+-----+
3 rows in set (0.03 sec)

mysql> select * from a join b using(id);
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | albert |  18 |
|  2 | bella  |  19 |
|  3 | amy    |  25 |
+----+--------+-----+
3 rows in set (0.04 sec)

自然左外连接

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

mysql> select * from a natural left outer join b;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | albert |   18 |
|  2 | bella  |   19 |
|  3 | amy    |   25 |
|  4 | forier | NULL |
+----+--------+------+
4 rows in set (0.04 sec)

mysql> select * from a natural left join b;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | albert |   18 |
|  2 | bella  |   19 |
|  3 | amy    |   25 |
|  4 | forier | NULL |
+----+--------+------+
4 rows in set (0.03 sec)

mysql> select * from a left join b using(id);
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | albert |   18 |
|  2 | bella  |   19 |
|  3 | amy    |   25 |
|  4 | forier | NULL |
+----+--------+------+
4 rows in set (0.03 sec)

自然右外连接

自然左外连接基础语法是a natural right outer join b，其中的outer可以省略，它也不能加连接条件，其作用与自然左外连接相反，其实就是右外连接省略掉using子句：

mysql> select * from a natural right outer join b;
+----+-----+--------+
| id | age | name   |
+----+-----+--------+
|  1 |  18 | albert |
|  2 |  19 | bella  |
|  3 |  25 | amy    |
|  5 |  70 | NULL   |
+----+-----+--------+
4 rows in set (0.04 sec)

mysql> select * from a natural right join b;
+----+-----+--------+
| id | age | name   |
+----+-----+--------+
|  1 |  18 | albert |
|  2 |  19 | bella  |
|  3 |  25 | amy    |
|  5 |  70 | NULL   |
+----+-----+--------+
4 rows in set (0.04 sec)

mysql> select * from a right join b using(id);
+----+-----+--------+
| id | age | name   |
+----+-----+--------+
|  1 |  18 | albert |
|  2 |  19 | bella  |
|  3 |  25 | amy    |
|  5 |  70 | NULL   |
+----+-----+--------+
4 rows in set (0.04 sec)

STRAIGHT_JOIN

STRAIGHT_JOIN的基础语法是a STRAIGHT_JOIN b，确实没有找到这种连接的中文说法，不过它与内连接几乎一样，只是它总是把左侧a表作为驱动表优先读入，它只能加on子句，无法使用using子句，在Sql优化的过程中常常使用，也就是拒绝了Mysql的语句优化，而使用自己指定的顺序来连接表格，不过使用时需慎重，你得比Mysql聪明才可以！

mysql> select * from a straight_join b on a.id = b.id;
+----+--------+----+-----+
| id | name   | id | age |
+----+--------+----+-----+
|  1 | albert |  1 |  18 |
|  2 | bella  |  2 |  19 |
|  3 | amy    |  3 |  25 |
+----+--------+----+-----+
3 rows in set (0.04 sec)

逗号分隔连接表

在from之后用逗号分隔的两个表格像极了内连接，只不过用逗号分隔的表不能使用子句连接，只可以用where来做条件筛选，不过作用之后的结果是一致的，可以对比看一下：

mysql> select * from a join b on a.id = b.id;
+----+--------+----+-----+
| id | name   | id | age |
+----+--------+----+-----+
|  1 | albert |  1 |  18 |
|  2 | bella  |  2 |  19 |
|  3 | amy    |  3 |  25 |
+----+--------+----+-----+
3 rows in set (0.04 sec)

mysql> select * from a, b where a.id = b.id;
+----+--------+----+-----+
| id | name   | id | age |
+----+--------+----+-----+
|  1 | albert |  1 |  18 |
|  2 | bella  |  2 |  19 |
|  3 | amy    |  3 |  25 |
+----+--------+----+-----+
3 rows in set (0.04 sec)

各种连接对比

通过描述可能有些关系还是没理解太清楚，所以整理了下面的表格，对比的更清楚一点，其中[]中的内容在编写sql时可以省略：

连接类型	语法	不加条件	加ON子句	加USING子句	与内连接关系
内连接	a [INNER] JOIN b	两表中任意两条记录分别匹配，形成笛卡尔积	只按照条件匹配，所有列均显示	按照共有的列匹配，去除重复列	X
交叉连接	a [CROSS] JOIN b	两表中任意两条记录分别匹配，形成笛卡尔积	只按照条件匹配，所有列均显示	按照共有的列匹配，去除重复列	语法等价，完全相同
左外连接	a LEFT [OUTER] JOIN b	必须加条件，否则报语法错误	只按照条件匹配，并且包含a表中没有匹配上的记录	按照共有的列匹配，并且包含a表中没有匹配上的记录	额外包含a表中没有匹配上的记录
右外连接	a RIGHT [OUTER] JOIN b	必须加条件，否则报语法错误	只按照条件匹配，并且包含b表中没有匹配上的记录	按照共有的列匹配，并且包含b表中没有匹配上的记录	额外包含b表中没有匹配上的记录
一般自然连接	a NATURAL JOIN b	使用两表中共有的字段匹配	不能使用ON子句	不能使用USING子句	相当于内连接使用USING子句
自然左外连接	a NATURAL LEFT [OUTER] JOIN b	使用两表中共有的字段匹配，并且包含a表中没有匹配上的记录	不能使用ON子句	不能使用USING子句	相当于内连接使用USING子句，并且包含a表中没有匹配上的记录
自然右外连接	a NATURAL RIGHT [OUTER] JOIN b	使用两表中共有的字段匹配，并且包含b表中没有匹配上的记录	不能使用ON子句	不能使用USING子句	相当于内连接使用USING子句，并且包含b表中没有匹配上的记录
STRAIGHT_JOIN	a STRAIGHT_JOIN b	两表中任意两条记录分别匹配，形成笛卡尔积	只按照条件匹配，所有列均显示	不能使用USING子句	在内连接基础上确定读表顺序
逗号分隔表	a, b	两表中任意两条记录分别匹配，形成笛卡尔积	不能使用ON子句	不能使用USING子句	不能使用连接子句，只能使用Where筛选

总结

1. 总的来看外连接中的outer是最没有存在感的，凡是它出现的地方都可以省略
2. 黑魔法：a inner join b与a cross join b是等价的，后来我偶然间拼写错误发现a across join b也是可以的，另外a love join b也行，开始还以为发现了bug，后来再理解应该是拼错的单词作了表a的别名，虚惊一场！
3. 通过上面的表格发现每种连接貌似都和内连接扯上了关系，那就以内连接为基础，通过扩展来记忆也是不错的
4. 如果表格不够清晰，换成思维导图或许会更好一些