圖解 SQL 裡的各種 JOIN
作者：碼志 原文：
前言
約定
常用的 JOIN
延伸用法
總結
更新：更多的 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）中能關聯起來的資料連線後返回。
文氏圖：
示例查詢：
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 的別名，下同。
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)
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)
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 的種類和概念了，看一下它們的合影：
有沒有感覺少了些什麼，學數學集合時完全不止這幾種情況？確實如此，繼續看。
延伸用法
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)
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)
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 學習。
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。
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 來達到相同的效果；
參考
Visual Representation of SQL Joins
How to do a FULL OUTER JOIN in MySQL?
SQL JOINS Slide Presentation
SQL Self Join
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31555484/viewspace-2565055/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [資料庫][SQL]圖解各種連線join資料庫SQL圖解
- 一張圖看懂 SQL 的各種 join 用法SQL
- SQL語句中不同的連線JOIN及SQL中join的各種用法SQL
- 一張圖說明SQL的join用法SQL
- 幽默：SQL Join形象解釋SQL
- oracle知識整理(1) union和union all的區別,left join和right join的區別(各種join的區別)Oracle
- sql的left join 、right join 、inner join之間的區別SQL
- 【SQL】13 SQL 別名、SQL 連線(JOIN)、SQL INNER JOIN 關鍵字、SQL LEFT JOIN 關鍵字、SQL RIGHT JOIN 關鍵字、SQL FULL OUTER JSQL
- 多表連線的三種方式詳解 hash join、merge join、 nested loopOOP
- sql：left join和join區別SQL
- SQL Server各種日期計算方法SQLServer
- Android 截圖的各種騷操作Android
- 一張圖搞定七種 JOIN 關係
- sql語句中JOIN ON 的使用SQL
- 面試題裡的那些各種手寫面試題
- Android 的各種 Drawable 詳解Android
- echart 各種圖實現
- SQL Server Left joinSQLServer
- Sql 之 join 001SQL
- SQL not exist out joinSQL
- 一張圖解釋各種資料庫型別圖解資料庫型別
- 軟體開發各個階段用到的各種圖
- Spark SQL中出現 CROSS JOIN 問題解決SparkSQLROS
- 康威定律的各種解讀 - ThinkingLabsThinking
- 趣圖：21 副 GIF 動圖讓你瞭解各種數學概念
- 各種原始碼實現，你想要的這裡都有原始碼
- sql統計各種奇葩的資料庫表資料SQL資料庫
- Django model select的各種用法詳解Django
- Spark SQL三種join和資料傾斜的產生和解決辦法SparkSQL
- 各種各樣的映象加速
- 如何使用R語言在SAP Analytics Cloud裡繪製各種統計圖表R語言Cloud
- 【SQL】18 SQL NULL 函式、SQL 通用資料型別、SQL 用於各種資料庫的資料型別SQLNull函式資料型別資料庫
- TaroEcharts-各種圖表在Taro中的實踐Echarts
- 用各種方法解01揹包
- SQL稽核 | 如何快速使用 SQLE 稽核各種型別的資料庫SQL型別資料庫
- SQL | JOIN 型別使用介紹SQL型別
- SQL 優先順序join＞whereSQL
- python pandas Join SQL⻛格合併PythonSQL