圖解MySQL里的各種 JOIN，看完不懂來找我！
從業以來主要在做客戶端，用到的數據庫都是表結構比較簡單的 SQLite，以我那還給老師一大半的 SQL 水平倒也能對付?，F在偶爾需要到后臺的 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
1、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_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 的別名，下同。
2、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_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)
3、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_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)
4、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_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 的種類和概念了，看一下它們的合影：
小結
有沒有感覺少了些什么，學數學集合時完全不止這幾種情況?確實如此，繼續看。
延伸用法
1、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_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)
2、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_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)
3、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_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 學習。
1、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。
2、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)
