SQL - 连接关系 - 内连接，左连接，右连接，全连接

之前虽然一直在用连接语法，但是从来没有系统的总结过，今天抽空总结一下。

先准备下面讲解过程中需要用到的两张表，这两张表尽可能的简单，能让大家理解。

user 表：

id	name	age
1	Allen	16
2	June	18
3	Jhon	28
4	Amy	45
5	Jack	67

order 表：

id	user_id	order_no	create_time
1	1	00001	2018-07-03 16:37:15
2	3	00002	2018-07-03 16:39:16
3	5	00003	2018-07-03 16:39:24
4	7	00004	2018-07-03 16:39:32
5	9	00005	2018-07-03 16:39:40

1. 内连接

内连接是根据连接条件，取多个表相互匹配的所有行。 简单来理解就是多个表在连接条件上的交集。 

它有两种写法，隐式写法（反而是我们最常用的写法）：

SELECT A.*, B.order_no, B.create_time FROM `user` A, `order` B WHERE A.id=B.user_id;

id	name	age	order_no	create_time
1	Allen	16	00001	2018-07-03 16:37:15
3	Jhon	28	00002	2018-07-03 16:39:16
5	Jack	67	00003	2018-07-03 16:39:24

结果中返回了 user 表 id 与 order 表 user_id 相匹配的所有行。其他行不显示。

还有一种显示写法 （Inner Join）

SELECT A.*, B.order_no, B.create_time FROM `user` AS A INNER JOIN `order` AS B ON A.id=B.user_id;

2. 左外连接

左外连接是显示左表的所有行，右表只显示匹配连接条件的行，在右表中没有匹配的行中，选择列字段值为null。

结果行数为左表查询出的行数。

SELECT A.*, B.id AS order_id, B.create_time FROM `user` AS A LEFT JOIN `order` AS B ON A.id=B.user_id;

name	age	order_id	create_time
Allen	16	1	2018-07-03 16:37:15
Jhon	28	2	2018-07-03 16:39:16
Jack	67	3	2018-07-03 16:39:24
June	18		
Amy	45		

3. 右外连接

右外连接是显示右表中的所有行，左表只显示匹配连接条件的行，在左表中没有匹配的行中，选择列字段值为null。

正好与左连接相反，结果行数为右表查询出的行数。

SELECT A.*, B.id AS order_id, B.create_time FROM `user` AS A RIGHT JOIN `order` AS B ON A.id=B.user_id;

id	name	age	order_id	create_time
1	Allen	16	1	2018-07-03 16:37:15
3	Jhon	28	2	2018-07-03 16:39:16
5	Jack	67	3	2018-07-03 16:39:24
			4	2018-07-03 16:39:32
			5	2018-07-03 16:39:40

4. 全外连接

全外连接其实就是返回左连接和右连接中的所有行。表之间的匹配行为基表，当一个表中的某行在另一个表中没有匹配行时，则另一个表中的选择列在连接到该行时为值null。

SELECT A.*, B.order_no, B.create_time FROM `user` AS A FULL JOIN `order` AS B ON A.id=B.user_id;

很不幸，Mysql不支持全连接（FULL JOIN），这句话我并没有测试。

但是可以通过另外一种办法来实现全连接（UNION）：

SELECT A.*, B.order_no, B.create_time FROM `user` AS A LEFT JOIN `order` AS B ON A.id=B.user_id
UNION 
SELECT A.*, B.order_no, B.create_time FROM `user` AS A RIGHT JOIN `order` AS B ON A.id=B.user_id;

id	name	age	order_no	create_time
1	Allen	16	00001	2018-07-03 16:37:15
3	Jhon	28	00002	2018-07-03 16:39:16
5	Jack	67	00003	2018-07-03 16:39:24
2	June	18		
4	Amy	45		
			00004	2018-07-03 16:39:32
			00005	2018-07-03 16:39:40

用过 union 语法将左连接和右连接的结果合并，即获得了全连接的结果。

全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外 UNION 右外”。

5. where 与 on 后面的条件

我们来先看一个例子：

SELECT A.*, B.order_no, B.create_time FROM `user` AS A LEFT JOIN `order` AS B ON A.id=B.user_id WHERE A.age > 18;

id	name	age	order_no	create_time
3	Jhon	28	00002	2018-07-03 16:39:16
5	Jack	67	00003	2018-07-03 16:39:24
4	Amy	45		

若我们将上一个SQL语句中 where 后面的条件放到 on 后面：

SELECT A.*, B.order_no, B.create_time FROM `user` AS A LEFT JOIN `order` AS B ON A.id=B.user_id AND A.age > 18;

id	name	age	order_no	create_time
3	Jhon	28	00002	2018-07-03 16:39:16
5	Jack	67	00003	2018-07-03 16:39:24
1	Allen	16		
2	June	18		
4	Amy	45		

结果不一样！结果不一样！结果不一样！第1个语句返回的结果是可以理解的，第二个结果完全无法去理解，虽然执行不会报错。因此，我们在写SQL语句的是要要注意了。

一般我们会遵循以下规则：

1. on 后面只跟连接条件。

2. 对于中间表的筛选条件放在 where 后面。

先写到这里，后面会补充 SQL 连接的原理：笛卡尔积。

可以先参考这篇文章 SQL的多表查询(笛卡尔积原理)