Sec Hotspot 首页  排行榜  收藏本站  技术博客  RSS
统计信息
已收录文章数量:11205 篇
已收录公众号数量:89 个
本站文章为爬虫采集,如有侵权请告知
本周热门文章
使用msf入侵多层级网络架构   2020.06.01 08:00:32  77
关于站库分离渗透思路总结   2020.05.28 18:37:17  61
Fastjson <=1.2.68 远程代码执行漏洞   2020.06.02 00:00:32  58
铁头娃的渗透测试   2020.05.31 09:49:53  58
使用yarGen提取Linux恶意脚本特征   2020.05.30 18:00:34  55
“Sauron Locker”家族病毒新变种   2020.05.31 18:00:11  52
已收录微信公众号
网信中国 区块链大本营 白说区块链 区块链投资家 区块链官微 区块链铅笔Blockchain HACK学习呀 二道情报贩子 合天智汇 小白帽学习之路 小米安全中心 弥天安全实验室 SAINTSEC SecPulse安全脉搏 TideSec安全团队 360安全卫士 游侠安全网 计算机与网络安全 安全祖师爷 安全学习那些事 腾讯安全联合实验室 黑客技术与网络安全 安全圈 腾讯御见威胁情报中心 Python开发者 Python之禅 编程派 Python那些事 Python程序员 安全威胁情报 吾爱破解论坛 行长叠报 安在 i春秋 嘶吼专业版 E安全 MottoIN 网信防务 网安杂谈 数说安全 互联网安全内参 漏洞战争 安全分析与研究 邑安全 ChaMd5安全团队 天融信阿尔法实验室 安全牛 SecWiki 安全学术圈 信安之路 漏洞感知 浅黑科技 Secquan圈子社区 奇安信集团 奇安信 CERT 国舜股份 雷神众测 盘古实验室 美团安全应急响应中心 瓜子安全应急响应中心 顺丰安全应急响应中心 蚂蚁金服安全响应中心 携程安全应急响应中心 滴滴安全应急响应中心 字节跳动安全中心 百度安全应急响应中心 腾讯安全应急响应中心 网易安全应急响应中心 OPPO安全应急响应中心 京东安全应急响应中心 Bypass CNNVD安全动态 安恒应急响应中心 天融信每日安全简报 奇安信威胁情报中心 看雪学院 黑白之道 水滴安全实验室 安全客 木星安全实验室 云鼎实验室 绿盟科技安全预警 白帽汇 深信服千里目安全实验室 腾讯玄武实验室 长亭安全课堂 FreeBuf 绿盟科技 nmask
秒懂!图解MySQL的各种 JOIN
本文来自公众号:Python之禅   2020.05.21 18:15:50


作者:码志

链接: https://mazhuang.org/2017/09/11/joins-in-sql/


从业以来主要在做客户端,用到的数据库都是表结构比较简单的 SQLite,以我那还给老师一大半的 SQL 水平倒也能对付。现在偶尔需要到后台的 SQL Server 里追查一些数据问题,就显得有点捉襟见肘了,特别是各种 JOIN,有时候傻傻分不清楚,于是索性弄明白并做个记录。


前言

在各种问答社区里谈及 SQL 里的各种 JOIN 之间的区别时,最被广为引用的是 CodeProject 上 C.L. Moffatt 的文章 Visual Representation of SQL Joins,他确实讲得简单明了,使用文氏图来帮助理解,效果明显。本文将沿用他的讲解方式,稍有演绎,可以视为该文较为粗糙的中译版。

约定

下文将使用两个数据库表 Table_A 和 Table_B 来进行示例讲解,其结构与数据分别如下:
mysql> SELECT * FROM Table_A ORDER BY PK ASC;
+----+---------+
| PK | Value   |
+----+---------+
|  1 | both ab |
|  2 | only a  |
+----+---------+
2 rows in set (0.00 sec)

mysql> SELECT * from Table_B ORDER BY PK ASC;
+----+---------+
| PK | Value   |
+----+---------+
|  1 | both ab |
|  3 | only b  |
+----+---------+
2 rows in set (0.00 sec)
其中 PK 为 1 的记录在 Table_A 和 Table_B 中都有,2 为 Table_A 特有,3 为 Table_B 特有


常用的 JOIN

1、INNER JOIN

INNER JOIN 一般被译作内连接。内连接查询能将左表(表 A)和右表(表 B)中能关联起来的数据连接后返回。
文氏图:
INNER JOIN
示例查询:
SELECT A.PK AS A_PK, B.PK AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
INNER JOIN Table_B B
ON A.PK = B.PK;

查询结果:

+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
|    1 |    1 | both ab | both ab |
+------+------+---------+---------+
1 row in set (0.00 sec)
注:其中 A 为 Table_A 的别名,B 为 Table_B 的别名,下同。

2、 LEFT JOIN
LEFT JOIN 一般被译作左连接,也写作 LEFT OUTER JOIN。左连接查询会返回左表(表 A)中所有记录,不管右表(表 B)中有没有关联的数据。在右表中找到的关联数据列也会被一起返回。
文氏图:
LEFT JOIN
示例查询:
SELECT A.PK AS A_PK, B.PK AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK;

查询结果:

+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
|    1 |    1 | both ab | both ba |
|    2 | NULL | only a  | NULL    |
+------+------+---------+---------+
2 rows in set (0.00 sec)


3、RIGHT JOIN

RIGHT JOIN 一般被译作右连接,也写作 RIGHT OUTER JOIN。右连接查询会返回右表(表 B)中所有记录,不管左表(表 A)中有没有关联的数据。在左表中找到的关联数据列也会被一起返回。
文氏图:
RIGHT JOIN
示例查询:
SELECT A.PK AS A_PK, B.PK AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK;

查询结果:

+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
|    1 |    1 | both ab | both ba |
| NULL |    3 | NULL    | only b  |
+------+------+---------+---------+
2 rows in set (0.00 sec)


4、FULL OUTER JOIN

FULL OUTER JOIN 一般被译作外连接、全连接,实际查询语句中可以写作 FULL OUTER JOIN 或 FULL JOIN。外连接查询能返回左右表里的所有记录,其中左右表里能关联起来的记录被连接后返回。
文氏图:
FULL OUTER JOIN
示例查询:
SELECT A.PK AS A_PK, B.PK AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK;

查询结果:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN Table_B B
ON A.PK = B.PK'
at line 4
注:我当前示例使用的 MySQL 不支持 FULL OUTER JOIN。
应当返回的结果(使用 UNION 模拟):
mysql> SELECT *
-> FROM Table_A
-> LEFT JOIN Table_B
-> ON Table_A.PK = Table_B.PK
-> UNION ALL
-> SELECT *
-> FROM Table_A
-> RIGHT JOIN Table_B
-> ON Table_A.PK = Table_B.PK
-> WHERE Table_A.PK IS NULL;
+------+---------+------+---------+
| PK   | Value   | PK   | Value   |
+------+---------+------+---------+
|    1 | both ab |    1 | both ba |
|    2 | only a  | NULL | NULL    |
| NULL | NULL    |    3 | only b  |
+------+---------+------+---------+
3 rows in set (0.00 sec)


小结

以上四种,就是 SQL 里常见 JOIN 的种类和概念了,看一下它们的合影:
小结
有没有感觉少了些什么,学数学集合时完全不止这几种情况?确实如此,继续看。


延伸用法

1、LEFT JOIN EXCLUDING INNER JOIN

返回左表有但右表没有关联数据的记录集。
文氏图:
LEFT JOIN EXCLUDING INNER JOIN
示例查询:
SELECT A.PK AS A_PK, B.PK AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK
WHERE B.PK IS NULL ;
查询结果:
+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
|    2 | NULL | only a  | NULL    |
+------+------+---------+---------+
1 row in set (0.01 sec)


2、RIGHT JOIN EXCLUDING INNER JOIN

返回右表有但左表没有关联数据的记录集。
文氏图:
RIGHT JOIN EXCLUDING INNER JOIN
示例查询:
SELECT A.PK AS A_PK, B.PK AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL ;

查询结果:

+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
| NULL |    3 | NULL    | only b  |
+------+------+---------+---------+
1 row in set (0.00 sec)


3、FULL OUTER JOIN EXCLUDING INNER JOIN

返回左表和右表里没有相互关联的记录集。
文氏图:
FULL OUTER JOIN EXCLUDING INNER JOIN
示例查询:
SELECT A.PK AS A_PK, B.PK AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL
OR B.PK IS NULL ;

因为使用到了 FULL OUTER JOIN,MySQL 在执行该查询时再次报错。

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL
OR B.PK IS NULL'
at line 4

应当返回的结果(用 UNION 模拟):

mysql> SELECT *
-> FROM Table_A
-> LEFT JOIN Table_B
-> ON Table_A.PK = Table_B.PK
-> WHERE Table_B.PK IS NULL
-> UNION ALL
-> SELECT *
-> FROM Table_A
-> RIGHT JOIN Table_B
-> ON Table_A.PK = Table_B.PK
-> WHERE Table_A.PK IS NULL;
+------+--------+------+--------+
| PK   | Value  | PK   | Value  |
+------+--------+------+--------+
|    2 | only a | NULL | NULL   |
| NULL | NULL   |    3 | only b |
+------+--------+------+--------+
2 rows in set (0.00 sec)



总结

以上七种用法基本上可以覆盖各种 JOIN 查询了。七种用法的全家福:

看着它们,我仿佛回到了当年学数学,求交集并集的时代……
顺带张贴一下 C.L. Moffatt 带 SQL 语句的图片,配合学习,风味更佳:


更多的 JOIN

除以上几种外,还有更多的 JOIN 用法,比如 CROSS JOIN(迪卡尔集)、SELF JOIN,可以参考 SQL JOINS Slide Presentation 学习。

1、CROSS JOIN

返回左表与右表之间符合条件的记录的迪卡尔集。
图示:
示例查询:
SELECT A.PK AS A_PK, B.PK AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
CROSS JOIN Table_B B;

查询结果:

+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
|    1 |    1 | both ab | both ba |
|    2 |    1 | only a  | both ba |
|    1 |    3 | both ab | only b  |
|    2 |    3 | only a  | only b  |
+------+------+---------+---------+
4 rows in set (0.00 sec)
上面讲过的几种 JOIN 查询的结果都可以用 CROSS JOIN 加条件模拟出来,比如 INNER JOIN 对应 CROSS JOIN ... WHERE A.PK = B.PK。

2、SELF JOIN

返回表与自己连接后符合条件的记录,一般用在表里有一个字段是用主键作为外键的情况。
比如 Table_C 的结构与数据如下:
+--------+----------+-------------+
| EMP_ID | EMP_NAME | EMP_SUPV_ID |
+--------+----------+-------------+
|   1001 | Ma       |        NULL |
|   1002 | Zhuang   |        1001 |
+--------+----------+-------------+
2 rows in set (0.00 sec)
EMP_ID 字段表示员工 ID,EMP_NAME 字段表示员工姓名,EMP_SUPV_ID 表示主管 ID。
示例查询:
现在我们想查询所有有主管的员工及其对应的主管 ID 和姓名,就可以用 SELF JOIN 来实现。
SELECT A.EMP_ID AS EMP_ID, A.EMP_NAME AS EMP_NAME,
B.EMP_ID AS EMP_SUPV_ID, B.EMP_NAME AS EMP_SUPV_NAME
FROM Table_C A, Table_C B
WHERE A.EMP_SUPV_ID = B.EMP_ID;
查询结果:
+--------+----------+-------------+---------------+
| EMP_ID | EMP_NAME | EMP_SUPV_ID | EMP_SUPV_NAME |
+--------+----------+-------------+---------------+
|   1002 | Zhuang   |        1001 | Ma            |
+--------+----------+-------------+---------------+
1 row in set (0.00 sec)


——End——


推荐阅读

微信真正的暗黑模式来袭!内附下载地址
60 个相见恨晚的神器工具
关注公众号,一起学习Python