图解 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 | FOX        |
|  2 | COP        |
|  3 | TAXI       |
|  4 | LINCION    |
|  5 | ARIZONA    |
|  6 | WASHINGTON |
|  7 | DELL       |
| 10 | LUCENT     |
+----+------------+
8 rows in set (0.00 sec)
mysql> SELECT * from Table_B ORDER BY PK ASC;
+----+-----------+
| PK | Value     |
+----+-----------+
|  1 | TROT      |
|  2 | CAR       |
|  3 | CAB       |
|  6 | MONUMENT  |
|  7 | PC        |
|  8 | MICROSOFT |
|  9 | APPLE     |
| 11 | SCOTCH    |
+----+-----------+
8 rows in set (0.00 sec)

常用的 JOIN
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 | FOX        | TROT     |

|    2 |    2 | COP        | CAR      |

|    3 |    3 | TAXI       | CAB      |

|    6 |    6 | WASHINGTON | MONUMENT |

|    7 |    7 | DELL       | PC       |

+------+------+------------+----------+

5 rows in set (0.00 sec)

注：其中A为Table_A的别名，B为Table_B的别名，下同。
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 | FOX        | TROT     |

|    2 |    2 | COP        | CAR      |

|    3 |    3 | TAXI       | CAB      |

|    4 | NULL | LINCION    | NULL     |

|    5 | NULL | ARIZONA    | NULL     |

|    6 |    6 | WASHINGTON | MONUMENT |

|    7 |    7 | DELL       | PC       |

|   10 | NULL | LUCENT     | NULL     |

+------+------+------------+----------+

8 rows in set (0.00 sec)

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 | FOX        | TROT      |

|    2 |    2 | COP        | CAR       |

|    3 |    3 | TAXI       | CAB       |

|    6 |    6 | WASHINGTON | MONUMENT  |

|    7 |    7 | DELL       | PC        |

| NULL |    8 | NULL       | MICROSOFT |

| NULL |    9 | NULL       | APPLE     |

| NULL |   11 | NULL       | SCOTCH    |

+------+------+------------+-----------+

8 rows in set (0.00 sec)

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 | FOX        |    1 | TROT      |

|    2 | COP        |    2 | CAR       |

|    3 | TAXI       |    3 | CAB       |

|    4 | LINCION    | NULL | NULL      |

|    5 | ARIZONA    | NULL | NULL      |

|    6 | WASHINGTON |    6 | MONUMENT  |

|    7 | DELL       |    7 | PC        |

|   10 | LUCENT     | NULL | NULL      |

| NULL | NULL       |    8 | MICROSOFT |

| NULL | NULL       |    9 | APPLE     |

| NULL | NULL       |   11 | SCOTCH    |

+------+------------+------+-----------+

11 rows in set (0.00 sec)

小结
以上四种，就是 SQL 里常见 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 |

+------+------+---------+---------+

|    4 | NULL | LINCION | NULL    |

|    5 | NULL | ARIZONA | NULL    |

|   10 | NULL | LUCENT  | NULL    |

+------+------+---------+---------+

3 rows in set (0.00 sec)

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 |    8 | NULL    | MICROSOFT |

| NULL |    9 | NULL    | APPLE     |

| NULL |   11 | NULL    | SCOTCH    |

+------+------+---------+-----------+

3 rows in set (0.00 sec)

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     |

+------+---------+------+-----------+

|    4 | LINCION | NULL | NULL      |

|    5 | ARIZONA | NULL | NULL      |

|   10 | LUCENT  | NULL | NULL      |

| NULL | NULL    |    8 | MICROSOFT |

| NULL | NULL    |    9 | APPLE     |

| NULL | NULL    |   11 | SCOTCH    |

+------+---------+------+-----------+

6 rows in set (0.00 sec)

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

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

补充说明
文中的图使用 Keynote 绘制；

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

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

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

还有更多的 JOIN 用法，比如 CROSS JOIN（迪卡尔集）、SELF JOIN，目前我还未在实际应用中遇到过，且不太好用图来表示，所以并未在本文中进行讲解。如果需要，可以参考 SQL JOINS Slide Presentation 学习。

参考
Visual Representation of SQL Joins

How to do a FULL OUTER JOIN in MySQL?

SQL JOINS Slide Presentation
1、具有1-5工作经验的，面对目前流行的技术不知从何下手，需要突破技术瓶颈的可以加群。
2、在公司待久了，过得很安逸，但跳槽时面试碰壁。需要在短时间内进修、跳槽拿高薪的可以加群。
3、如果没有工作经验，但基础非常扎实，对java工作机制，常用设计思想，常用java开发框架掌握熟练的，可以加群。
4、觉得自己很牛B，一般需求都能搞定。但是所学的知识点没有系统化，很难在技术领域继续突破的可以加群。

群号：高级架构群 647631030备注好信息！
6.阿里Java高级架构师免费大牛直播讲解知识点，分享知识，多年工作经验的梳理和总结，带着大家全面、科学地建立自己的技术体系和技术认知！

最后编辑于：2017.12.10 13:44:10

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,324评论 5赞 476
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,303评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,192评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,555评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,569评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,566评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,927评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,583评论 0赞 257
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,827评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,590评论 2赞 320
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,669评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,365评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,941评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,928评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,159评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,880评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,399评论 2赞 342

图解 SQL 里的各种 JOIN

推荐阅读更多精彩内容