本文标识 : 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;
数据结果:
左连接小结:
全连接,就是将要连接的各表的所有信息进行融合。简单粗暴的讲,就是取并集,用数学符号表示就是 集合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