图解这么形象，别再问SQL怎么写JOIN了！

最新推荐文章于 2023-03-28 15:14:20 发布
最新推荐文章于 2023-03-28 15:14:20 发布
阅读量155 收藏
点赞数 1
文章标签： mysql sql dbcp sqlserver hibernate

马壮 | 作者

闷骚的程序员 | 来源

https://mp.weixin.qq.com/s/oAECmAM5LYupcFHdfP8cDw

大家好，我是小z

总有同学被SQL里面的各种JOIN搞的头大，今天分享这篇文章，以图解的形式专门对各种连接做了清楚的解释，非常形象！

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

1

前言

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

2

约定

下文将使用两个数据库表 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特有。

3

常用的 JOIN

1.INNER JOIN

INNER JOIN 一般被译作内连接。内连接查询能将左表（表 A）和右表（表 B）中能关联起来的数据连接后返回。

文氏图：

示例查询：

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）中有没有关联的数据。在右表中找到的关联数据列也会被一起返回。

文氏图：

示例查询：


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）中有没有关联的数据。在左表中找到的关联数据列也会被一起返回。

文氏图：

示例查询：


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。外连接查询能返回左右表里的所有记录，其中左右表里能关联起来的记录被连接后返回。

文氏图：

示例查询：


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 的种类和概念了，看一下它们的合影：

有没有感觉少了些什么，学数学集合时完全不止这几种情况？确实如此，继续看。

4

延伸用法

1.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

返回右表有但左表没有关联数据的记录集。

文氏图：

示例查询：


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

返回左表和右表里没有相互关联的记录集。

文氏图：

示例查询：


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)

5

总结

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

看着它们，我仿佛回到了当年学数学，求交集并集的时代……

顺带张贴一下 C.L. Moffatt 带 SQL 语句的图片，配合学习，风味更佳：

6

补充：更多的 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)

补充说明：

  • 文中的图使用Keynote绘制

  • 个人的体会是 SQL 里的 JOIN 查询与数学里的求交集、并集等很像

  • SQLite不支持 RIGHT JOIN 和 FULL OUTER JOIN，可以使用 LEFT JOIN 和 UNION 来达到相同的效果

  • MySQL不支持 FULL OUTER JOIN，可以使用 LEFT JOIN 和 UNION 来达到相同的效果

●全面构建数据分析思维！

●取数，取数，取个屁啊！

后台回复“入群”即可加入小z数据干货交流群
数据不吹牛
关注
数据库基础-JOIN
03-19 494
JOIN分类 三、JOIN分类详解 INNER JOIN： 仅仅返回两个表中，匹配列相同的列值，所在行的数据。 SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1 LEFT OUTER JOIN： 左外连接：返回左表的所有数据，并且在右表中不能匹配的列值，其坐在行则使用...
图解SQL里面的各种join
L_R
04-07 1089
最近在网上看面经，再结合自己的一丢丢面试经验，觉得有必要在博客里总结一下这块知识： 这里我参考目录INNER JOINLEFT JOINRIGHT JOINFULL OUTER JOINLEFT JOIN EXCLUDING INNER JOINRIGHT JOIN EXCLUDING INNER JOINFULL OUTER JOIN EXCLUDING INNER JOIN 先来看看在SQL中...
手把手教你写 SQL Join 联接
Defonds 的专栏
09-27 1万+
在关系代数中，联接运算是由一个笛卡尔积运算和一个选取运算构成的。首先用笛卡尔积完成对两个数据集合的乘运算，然后对生成的结果集合进行选取运算，确保只把分别来自两个数据集合并且具有重叠部分的行合并在一起。联接的全部意义在于在水平方向上合并两个数据集合（通常是表），并产生一个新的结果集合，其方法是将一个数据源中的行于另一个数据源中和它匹配的行组合成一个新元组。本文从几个简单的例子里，手把手教你如何写内联接、左外联接、右外联接、全外联接以及交叉联接。
打破砂锅问到底：SQL中到底能不能写JOIN？
IT技术精选文摘
04-08 306
若有收获,请记得分享和转发哦之前我们了解了优化器的工作原理，相信你已经可以对单表的 SQL 语句进行索引的设计和调优工作。但除了单表的SQL 语句，还有两大类相对复杂的 SQL，多表 JOIN 和子查询语句，这就要在多张表上创建索引，难度相对提升不少。而很多开发人员下意识地认为 JOIN 会降低 SQL 的性能效率，所以就将一条多表 SQL 拆成单表的一条条查询，但这样...
图解 SQL 里的各种 JOIN
程序猿杂货铺
06-05 348
戳上方蓝字 “程序猿杂货铺” 关注我！你的关注意义重大！来源：http://t.cn/E2K1pfS阅读本文大概需要 12 分钟在各种问答社区里谈及 SQL 里的各种 J...
一张图看懂 SQL 的各种 join 用法
公众号：Java后端
02-02 321
作者 |C.L. Moffatt来源 | codeproject导读：下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN ...
数据库SQL笔试题目
hard_days的博客
10-09 2057
数据库SQL面试题
大厂面试快问快答，10分钟搞定MySQL夺命20问，你都能接住吗？
华科硕士，大厂打工中。编程问题咨询，请私我
07-19 4759
号外号外！《死磕 Java 并发编程》系列连载中，大家可以关注一波： 「死磕 Java 并发编程05」阿里面试失败后，一气之下我图解了Java中18把锁 「死磕 Java 并发编程04」说说Java Atomic 原子类的实现原理 「死磕 Java 并发编程03」阿里二面，面试官：说说 Java CAS 原理？ 「死磕 Java 并发编程02」面试官：说说什么是 Java 内存模型（JMM）？ 「死磕 Java 并发编程01」10张图告诉你Java并发多线程那些破事 先看下目录： 1说说.
不要再问我 in，exists 走不走索引了
Park33的博客
08-20 464
最近，有一个业务需求，给我一份数据 A ，把它在数据库 B 中存在，而又比 A 多出的部分算出来。由于数据比较杂乱，我这里简化模型。然后就会发现，我去，这不就是 not in ，not exists 嘛。那么问题来了，in, not in , exists , not exists 它们有什么区别，效率如何？曾经从网上听说，in 和 exists 不会走索引，那么事实真的是这样吗？带着疑问，我们研究下去。
SQL中join的用法
zn0999的博客
10-12 1034
第一种：Inner join 解释：产生的结果是A和B的交集（相同列里面的相同值）。内连接是最常见的一种连接，它也被称为普通连接，只连接匹配的行（仅对满足连接条件的CROSS中的列）。它又分为等值连接（连接条件运算符为"="）和不等值连接（连接条件运算符不为"="，例如between…and）。 SELECT *FROM b INNER JOIN A on b.name=A.name 第二种：Full Outer Join 解释：产生的结果是A和B的并集（如果没有相同的值会用null作为值）。FULL JO
SQL语句中JOIN的用法
zhangbeizhen18的博客
07-20 2万+
记录：256 写SQL最高境界：SELECT * FROM 表名。当然这是一句自嘲。探究一下SQL语句中JOIN的用法，直到经历这个场景，变得想验证一下究竟。 一、场景 把关系型数据库A中表TEST_TB01和TEST_TB02迁移到大数据平台M（MaxCompute大数据平台）。TEST_TB01单表1000万条记录，TEST_TB02单表80万条记录。 在关系型数据库中，TEST_TB01和TEST_TB02中有主键约束。在产生新增业务数据时，不会存在重复数据插入。但是，当数据迁移到大数据平
SQL的各种连接（cross join、inner join、full join)的用法理解
lxw1844912514的博客
01-23 1310
SQL中的连接可以分为内连接，外连接，以及交叉连接 。 1. 交叉连接CROSS JOIN 如果不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积； 举例,下列A、B、C 执行结果相同，但是效率不一样： A:SELECT * FROM table1 CROSS JOIN table2 B:SELECT * FROM table1,...
【SQL 复健】 join 的四种写法
最新发布
一个噗噗的博客
03-28 1111
sql中的 join
SQL Join各种内联外联说明
jerome
12-30 2092
Visual Representation of SQL JoinsC.L. Moffatt, 3 Feb 2009 CPOL   4.96 (406 votes)Rate this:This article describes SQL Joins in a visual manner, and also the most efficient way to write the visualized
SQL语法之JOIN
Redhat_yan
08-18 1079
学习SQL语法
sql中join的各种用法
weixin_42388255的博客
05-06 8359
sql中join的用法 sql中join的含义可以理解为单词“join”，用来连接两张表，join所有连接方式可以分为： 内连接，外连接，右连接，左连接，自然连接 上面这张图已经很清晰的表明了各种连接方式的语法。 如果多对与整体表的查询进行分类，按照下面表格进行： 内连接： ***内连接INNER JOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集，从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。join，inner join，有INNER JOIN，WHERE（等值连接），S
SQL中的几种JOIN
面向学姐编程
11-07 6639
洋洋洒洒写几百个字，不如上传一张示意图。现在都讲可视化嘛，图片最直观了。
简单说说SQL中Join的使用
架构栈
11-09 1万+
最近工作中，遇到了一起由于慢SQL引起DB CPU > 90% ，数据库hang住。。最终导致其他业务查询统统失败。       细看下来是由于几张大表Join关联查询引起的，故障本身很常见，不过让我想到有必要讲讲如何规避Join的问题。       以下的讨论都是基于数据库能力有限的前提下，否则后续的讨论就可以直接忽略了。首先，我们来谈谈SQL Join的使用场景1）如果系统存在高并发、分布式业
SQL语句中的join用法
热门推荐
julielele的博客
08-24 4万+
SQL中join的各种用法 1.自然连接（natural join） 自然连接将表中具有相同名称的列自动进行匹配，自然连接不必指定任何同等连接条件也不能认为指定哪些列需要被匹配，自然连接得到的结果表中，两表中名称相同的列只出现一次。 select * from employee natural join department; 2.内连接（inner join）:产生的结果是A和B的交集（相同列里...
图解SQL JOIN操作的简易说明
SQL_Joins.zip_sql joins这一资源包便是针对SQL JOIN操作的示例说明，它可能包含了一张名为SQL_Joins.jpg的图解文件，该图解文件能够为数据库开发者或数据分析师提供直观的连接操作理解。 SQL JOIN的基本概念： SQL...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值