圖解 SQL 裡的各種 JOIN

工匠小豬豬的技術世界發表於2019-01-28

從業以來主要在做客戶端，用到的資料庫都是表結構比較簡單的 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 來達到相同的效果；

假如你對我的文章感興趣，可以關注我的微信公眾號『悶騷的程式設計師』隨時閱讀更多內容。

參考

原始連結：圖解 SQL 裡的各種 JOIN

[資料庫][SQL]圖解各種連線join
2019-03-28
資料庫SQL圖解
一張圖看懂 SQL 的各種 join 用法
2019-04-01
SQL
SQL語句中不同的連線JOIN及SQL中join的各種用法
2018-12-17
SQL
MySQL的各種join
2014-11-18
MySql
sql join例項圖解
2016-11-07
SQL圖解
畫圖解釋 SQL join 語句
2014-08-19
圖解SQL
[CareerCup] 15.4 Types of Join 各種交
2016-04-05
各種TOP SQL
2009-02-05
SQL
sql的left join 命令詳解
2010-05-20
SQL
sql left join 和 right join解釋
2007-05-14
SQL
面試題裡的那些各種手寫
2019-04-10
面試題
sql中的join、left join、right join
2017-07-31
SQL
oracle知識整理(1) union和union all的區別,left join和right join的區別(各種join的區別)
2020-04-07
Oracle
關於SQL Server tempdb 的各種操作
2009-09-08
SQLServer
SQL join
2007-05-09
SQL
echart 各種圖實現
2020-10-30
sql之left join、right join、inner join的區別
2017-08-28
SQL
模擬oracle裡的各種型別的壞塊
2015-06-09
Oracle型別
多表連線的三種方式詳解 hash join、merge join、 nested loop
2021-02-03
OOP
sql的left join 、right join 、inner join之間的區別
2018-07-02
SQL
軟體開發各個階段用到的各種圖
2024-03-07
Android 截圖的各種騷操作
2019-05-22
Android
各種原始碼實現，你想要的這裡都有
2019-11-07
原始碼
Oracle SQL JOIN
2012-01-05
OracleSQL
SQL -- JOIN子句
2009-10-12
SQL
Android 的各種 Drawable 詳解
2021-09-09
Android
【SQL】13 SQL 別名、SQL 連線(JOIN)、SQL INNER JOIN 關鍵字、SQL LEFT JOIN 關鍵字、SQL RIGHT JOIN 關鍵字、SQL FULL OUTER J
2018-07-26
SQL
sql語句中JOIN ON 的使用
2019-03-15
SQL
sql的 INNER JOIN 語法
2009-10-12
SQL
SQL優化--用各種hints優化一條SQL
2012-04-16
SQL優化
windows的各種副檔名詳解
2013-08-06
Windows
SQL not exist out join
2019-03-15
SQL
sql優化講課中引出的各種問題！
2007-10-22
SQL優化
SQL中的Filter, join, semi-join等概念的釋義
2016-03-29
SQLFilter
Spark SQL中出現 CROSS JOIN 問題解決
2019-10-13
SparkSQLROS
純 CSS 建立各種不同的圖形形狀
2015-11-08
CSS
如何使用R語言在SAP Analytics Cloud裡繪製各種統計圖表
2020-04-11
R語言Cloud
SQL中聯表查詢操作（LEFT JOIN, RIGHT JOIN, INNER JOIN）
2012-10-24
SQL

圖解 SQL 裡的各種 JOIN

前言

約定

常用的 JOIN

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

小結

延伸用法

LEFT JOIN EXCLUDING INNER JOIN

RIGHT JOIN EXCLUDING INNER JOIN

FULL OUTER JOIN EXCLUDING INNER JOIN

總結

更新：更多的 JOIN

CROSS JOIN

SELF JOIN

補充說明

參考

相關文章