图解 SQL 里的各种 JOIN

从业以来主要在做客户端,用到的数据库都是表结构比较简单的 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

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_ValueFROM Table_A AINNER JOIN Table_B BON 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 的别名,下同.

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_ValueFROM Table_A ALEFT JOIN Table_B BON 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)

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_ValueFROM Table_A ARIGHT JOIN Table_B BON 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)

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_ValueFROM Table_A AFULL OUTER JOIN Table_B BON 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 BON 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 的种类和概念了,看一下它们的合影：

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

延伸用法

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_ValueFROM Table_A ALEFT JOIN Table_B BON A.PK = B.PKWHERE B.PK IS NULL;

查询结果：

+------+------+---------+---------+| A_PK | B_PK | A_Value | B_Value |+------+------+---------+---------+| 2 | NULL | only a | NULL |+------+------+---------+---------+1 row in set (0.01 sec)

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_ValueFROM Table_A ARIGHT JOIN Table_B BON A.PK = B.PKWHERE A.PK IS NULL;

查询结果：

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

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_ValueFROM Table_A AFULL OUTER JOIN Table_B BON A.PK = B.PKWHERE A.PK IS NULLOR 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 BON A.PK = B.PKWHERE A.PK IS NULLOR 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 查询了.七种用法的百口福：

SQL JOINS

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

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

SQL JOINS

更新：更多的 JOIN

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

CROSS JOIN

返回左表与右表之间符合条件的记录的迪卡尔集.

图示：

CORSS JOIN

示例查询：

SELECT A.PK AS A_PK, B.PK AS B_PK, A.Value AS A_Value, B.Value AS B_ValueFROM Table_A ACROSS 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.

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_NAMEFROM Table_C A, Table_C BWHERE 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)

补充说明

1. 文中的图使用 Keynote 绘制；

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

3. SQLite 不支持 RIGHT JOIN 和 FULL OUTER JOIN,可以使用 LEFT JOIN 和 UNION 来达到相同的效果；

4. MySQL 不支持 FULL OUTER JOIN,可以使用 LEFT JOIN 和 UNION 来达到相同的效果；

参考

• Visual Representation of SQL Joins

• How to do a FULL OUTER JOIN in MySQL?

• SQL JOINS Slide Presentation

• SQL Self Join

1、具有1-5工作经验的,面对目前流行的技术不知从何下手,需要突破技术瓶颈的可以加群.

2、在公司待久了,过得很安适,但跳槽时面试碰壁.需要在短时间内进修、跳槽拿高薪的可以加群.

3、如果没有工作经验,但基础非常扎实,对java工作机制,常用设计思想,常用java开发框架掌握熟练的,可以加群.

4、觉得本身很牛B,一般需求都能搞定.但是所学的知识点没有系统化,很难在技术领域继续突破的可以加群.

5. 群号：高级架构群 647631030备注好信息!

6.阿里Java高级架构师直播讲解知识点,分享知识,多年工作经验的梳理和总结,带着大家全面、科学地建立本身的技术体系和技术认知!