轉(zhuǎn)載自 圖解 SQL 里的各種 JOIN
從業(yè)以來(lái)主要在做客戶端蝇摸，用到的數(shù)據(jù)庫(kù)都是表結(jié)構(gòu)比較簡(jiǎn)單的 SQLite怖竭，以我那還給老師一大半的 SQL 水平倒也能對(duì)付〉舛現(xiàn)在偶爾需要到后臺(tái)的 SQL Server 里追查一些數(shù)據(jù)問(wèn)題抱既，就顯得有點(diǎn)捉襟見(jiàn)肘了剪返，特別是各種 JOIN迫卢，有時(shí)候傻傻分不清楚倚搬，于是索性弄明白并做個(gè)記錄。
前言
在各種問(wèn)答社區(qū)里談及 SQL 里的各種 JOIN 之間的區(qū)別時(shí)乾蛤，最被廣為引用的是 CodeProject 上 C.L. Moffatt 的文章 Visual Representation of SQL Joins每界，他確實(shí)講得簡(jiǎn)單明了，使用文氏圖來(lái)幫助理解家卖，效果明顯盆犁。本文將沿用他的講解方式，稍有演繹篡九，可以視為該文較為粗糙的中譯版谐岁。
約定
下文將使用兩個(gè)數(shù)據(jù)庫(kù)表 Table_A 和 Table_B 來(lái)進(jìn)行示例講解，其結(jié)構(gòu)與數(shù)據(jù)分別如下：
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 一般被譯作內(nèi)連接沛善。內(nèi)連接查詢能將左表（表 A）和右表（表 B）中能關(guān)聯(lián)起來(lái)的數(shù)據(jù)連接后返回航揉。
文氏圖：
示例查詢：
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;
查詢結(jié)果：
+------+------+---------+---------+
| 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尤蛮。左連接查詢會(huì)返回左表（表 A）中所有記錄媳友，不管右表（表 B）中有沒(méi)有關(guān)聯(lián)的數(shù)據(jù)。在右表中找到的關(guān)聯(lián)數(shù)據(jù)列也會(huì)被一起返回产捞。
文氏圖：
示例查詢：
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;
查詢結(jié)果：
+------+------+---------+---------+
| 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。右連接查詢會(huì)返回右表（表 B）中所有記錄坯临，不管左表（表 A）中有沒(méi)有關(guān)聯(lián)的數(shù)據(jù)焊唬。在左表中找到的關(guān)聯(lián)數(shù)據(jù)列也會(huì)被一起返回。
文氏圖：
示例查詢：
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;
查詢結(jié)果：
+------+------+---------+---------+
| 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 一般被譯作外連接看靠、全連接赶促，實(shí)際查詢語(yǔ)句中可以寫作 FULL OUTER JOIN 或 FULL JOIN。外連接查詢能返回左右表里的所有記錄挟炬，其中左右表里能關(guān)聯(lián)起來(lái)的記錄被連接后返回鸥滨。
文氏圖：
示例查詢：
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;
查詢結(jié)果：
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
注：我當(dāng)前示例使用的 MySQL 不支持 FULL OUTER JOIN嗦哆。
應(yīng)當(dāng)返回的結(jié)果（使用 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)
小結(jié)
以上四種，就是 SQL 里常見(jiàn) JOIN 的種類和概念了爵赵，看一下它們的合影：
有沒(méi)有感覺(jué)少了些什么吝秕，學(xué)數(shù)學(xué)集合時(shí)完全不止這幾種情況泊脐？確實(shí)如此空幻，繼續(xù)看。
延伸用法
LEFT JOIN EXCLUDING INNER JOIN
返回左表有但右表沒(méi)有關(guān)聯(lián)數(shù)據(jù)的記錄集容客。
文氏圖：
示例查詢：
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;
查詢結(jié)果：
+------+------+---------+---------+
| 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
返回右表有但左表沒(méi)有關(guān)聯(lián)數(shù)據(jù)的記錄集秕铛。
文氏圖：
示例查詢：
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;
查詢結(jié)果：
+------+------+---------+---------+
| 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
返回左表和右表里沒(méi)有相互關(guān)聯(lián)的記錄集。
文氏圖：
示例查詢：
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;
因?yàn)槭褂玫搅?FULL OUTER JOIN缩挑，MySQL 在執(zhí)行該查詢時(shí)再次報(bào)錯(cuò)但两。
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
應(yīng)當(dāng)返回的結(jié)果（用 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)
總結(jié)
以上七種用法基本上可以覆蓋各種 JOIN 查詢了。七種用法的全家福：
看著它們供置，我仿佛回到了當(dāng)年學(xué)數(shù)學(xué)谨湘，求交集并集的時(shí)代……
順帶張貼一下 C.L. Moffatt 帶 SQL 語(yǔ)句的圖片，配合學(xué)習(xí)芥丧，風(fēng)味更佳：
更新：更多的 JOIN
除以上幾種外紧阔，還有更多的 JOIN 用法，比如 CROSS JOIN（迪卡爾集）续担、SELF JOIN擅耽，可以參考 SQL JOINS Slide Presentation 學(xué)習(xí)。
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;
查詢結(jié)果：
+------+------+---------+---------+
| 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)
上面講過(guò)的幾種 JOIN 查詢的結(jié)果都可以用 CROSS JOIN 加條件模擬出來(lái)乖仇，比如 INNER JOIN 對(duì)應(yīng) CROSS JOIN ... WHERE A.PK = B.PK。
SELF JOIN
返回表與自己連接后符合條件的記錄询兴，一般用在表里有一個(gè)字段是用主鍵作為外鍵的情況乃沙。
比如 Table_C 的結(jié)構(gòu)與數(shù)據(jù)如下：
+--------+----------+-------------+
| 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崔涂。
示例查詢：
現(xiàn)在我們想查詢所有有主管的員工及其對(duì)應(yīng)的主管 ID 和姓名，就可以用 SELF JOIN 來(lái)實(shí)現(xiàn)始衅。
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;
查詢結(jié)果：
+--------+----------+-------------+---------------+
| EMP_ID | EMP_NAME | EMP_SUPV_ID | EMP_SUPV_NAME |
+--------+----------+-------------+---------------+
| 1002 | Zhuang | 1001 | Ma |
+--------+----------+-------------+---------------+
1 row in set (0.00 sec)
補(bǔ)充說(shuō)明
文中的圖使用 Keynote 繪制冷蚂；
個(gè)人的體會(huì)是 SQL 里的 JOIN 查詢與數(shù)學(xué)里的求交集、并集等很像汛闸；
SQLite 不支持 RIGHT JOIN 和 FULL OUTER JOIN蝙茶，可以使用 LEFT JOIN 和 UNION 來(lái)達(dá)到相同的效果；
MySQL 不支持 FULL OUTER JOIN诸老，可以使用 LEFT JOIN 和 UNION 來(lái)達(dá)到相同的效果隆夯；
假如你對(duì)我的文章感興趣，可以關(guān)注我的微信公眾號(hào) isprogrammer 隨時(shí)閱讀更多內(nèi)容。
