多表操作之列关联（二）

2020 年 1 月 2 日
筆記

本文标识 : MQ0014

本文编辑 : 长安月下赏美人儿

编程工具 : MySQL、DBeaver

阅读时长 : 8分钟

前言

上周和小伙伴儿们一起分享了《多表操作之列关联（一）》，主要涉及到交叉连接和内连接，此篇则详细介绍外连接的相关信息。也许会有困惑，为啥单独介绍外连接，是因为其骨骼惊奇么？！小编在此郑重声明，之所以单独介绍外连接，主要是因为其特别有趣，特别精彩，尤其是里面的左连接更是在日常工作中被频繁翻牌子！

外连接分类

外连接根据连接方式的不同，分为两种类。其中，左连接和右连接，原理一致，但也分两种不同的情况，全连接亦然如此。具体类别如下图所示。

左连接、右连接
全连接

图示所示：

左连接

左连接与右连接原理一致，故搞懂左连接两种不同场景，也将顺利解锁右连接的奥秘。

LEFT JOIN：以左表为主表，通过相同意义的字段与右表连接，返回左表的全部数据行，以及右表中匹配的记录，如果右表中没有匹配，则用字段 NULL 来填充。听起来很简单有没有？！但是，事情的经过并不是这个样子的，实践是检验真理的唯一标准，实际操作过程中的坑坑洼洼在所难免。

场景一：LEFT JOIN 包含两表交集

# LEFT JOIN 场景一基础语法：  SELECT <select_list>  FROM TableA A  LEFT JOIN TableB B  ON A.Key = B.Key；

在左连接场景一中，根据两表连接关键字的不同，又细分为3种情况。

1、一对一

通俗的讲，A 表与 B 表连接的关键字内的信息值，两者一一对应。即 A.Key 中的信息，在 B.Key 中有且仅有唯一对应值。譬如，前者里面有 1 ，后者里面有且仅有一个 1 。

数据背景：数据表 t1 ，数据表 t2

SELECT * FROM t1 A  LEFT JOIN t2 B  ON A.id=B.id;

数据结果：

2、一对多

通俗的讲，A 表与 B 表连接的关键字内的信息值，前者关键字内信息值各自独立，后者关键字内信息值出现多个重复值。即 A.Key 中的信息，在 B.Key 中有多个对应值。譬如，前者里面有 1 ，后者里面有 2 个 1 。

数据背景：数据表 t1 ，数据表 t2

SELECT * FROM t1 A  LEFT JOIN t2 B  ON A.id=B.id;

数据结果：

逻辑解析：

（1）数据表 t1 中，有 4 条数据，且 id 各自独立；

（2）数据表 t2 中，有 4 条数据，但 id=2 的数据有 2 条；

（3）当 id=2 的时候，在表 t2 中有 2 条满足和表 t1 中 id=2 进行匹配；

（4）故，返回的结果是 5 条，其中 2 条数据 id=2。

3、多对多

通俗的讲，A 表与 B 表连接的关键字内的信息值，前者与后者关键字内信息值出现多个重复值。即 A.Key 中有重复信息，在 B.Key 中有多个对应值。譬如，前者里面有 2 个 1 ，后者里面有 2 个 1 。

数据背景：数据表 t1 ，数据表 t2

SELECT * FROM t1 A  LEFT JOIN t2 B  ON A.id=B.id;

数据结果：

逻辑解析：

（1）数据表 t1 中，有 6 条数据，且 id=1、id=2 各有 2 条数据；

（2）数据表 t2 中，有 7 条数据，且 id=1、id=2 各有 2 条数据；

（3）当 id=1 ，name=‘大毛’ 的时候，在表 t2 中有 2 条满足和表 t1 中 id=2 进行匹配，以此类推；

（4）故，返回的结果是 10 条，其中 id=1、id=2 各有 4 条数据。

场景二：LEFT JOIN 不包含两表交集

# LEFT JOIN 场景二基础语法：  SELECT <select_list>  FROM TableA A  LEFT JOIN TableB B  ON A.Key = B.Key  WHERE  B.Key IS NULL；

数据背景：数据表 t1 ，数据表 t2

#例1  SELECT * FROM t1 A  LEFT JOIN t2 B  ON A.id=B.id  WHERE B.id IS NULL;

数据结果：

#例2  SELECT * FROM t1 A  LEFT JOIN t2 B  ON A.name=B.name  WHERE B.name IS NULL;

数据结果：

左连接小结：

在进行多表连接之前，查看清楚连接的关键字是否唯一，如果不唯一，则要考虑清楚选择哪个字段作为关键字
在留存交集的情况下，考虑清楚三种不同的连接情况，确定想要的数据模式
如果想对右表进行限制，则一定要在 on 条件中进行
如果想对连接之后的数据进行过滤，则一定要在 where 条件中进行
如果没有 where 条件，无论 on 条件对两表怎么限制，左表的每一行至少会有一行的合成结果

全连接

全连接，就是将要连接的各表的所有信息进行融合。简单粗暴的讲，就是取并集，用数学符号表示就是集合A ∪ 集合 B

场景一：FULL OUTER JOIN 包含两表交集

# FULL  OUTER JOIN 场景一基础语法：  SELECT <select_list> FROM TableA A  LEFT JOIN TableB B  ON A.Key = B.Key  WHERE <限制条件>  UNION  SELECT <select_list> FROM TableA A  RIGHT JOIN TableB B  ON A.Key = B.Key  WHERE <限制条件>；

数据背景：数据表 t1 ，数据表 t2

SELECT * FROM t1  LEFT JOIN t2  ON t1.name=t2.name  UNION  SELECT * FROM t1  RIGHT JOIN t2  ON t1.name=t2.name;

数据结果：

注意：如果直接关联两表会如何？

（还记得之前学习的行关联么？！）

SELECT*FROM t1  UNION  SELECT * FROM t2;

数据结果：

场景二：FULL OUTER JOIN 不包含两表交集

# FULL OUTER JOIN 场景二基础语法：  SELECT <select_list> FROM TableA A  LEFT JOIN TableB B  ON B.Key IS NULL  UNION  SELECT <select_list> FROM TableA A  RIGHT JOIN TableB B  ON A.Key IS NULL；

数据背景：数据表 t1 ，数据表 t2

SELECT * FROM t1  LEFT JOIN t2  ON t1.name=t2.name  WHERE t2.name IS NULL  UNION  SELECT * FROM t1  RIGHT JOIN t2  ON t1.name=t2.name  WHERE t1.name IS NULL;

数据结果：

逻辑解析：

（1）数据表 t1 与 t2 中，以 name 为连接关键词，重复的数据有‘二毛’、‘小明’；

（2）两数据表左连接，且不包含交集

（3）两数据表右连接，且不包含交集

（4）对查询结果关联即可得到结果

全连接小结：

进行数据表全连接的同时，要注意是否包含交集

注意：关于全连接，有的资料显示，可以直接用 FULL OUTER JOIN 句式，但小编在 DBeaver 中尝试失败，感兴趣的小伙伴儿可以去尝试一下。

# FULL OUTER JOIN 场景一基础语法：  SELECT <select_list> FROM TableA A  FULL OUTER JOIN TableB B  ON A.Key=B.Key;    # FULL OUTER JOIN 场景二基础语法：  SELECT <select_list> FROM TableA A  FULL OUTER JOIN TableB B  WHERE A.Key IS NULL  OR B.Key IS NULL