圖解SQL裡的各種 JOIN，看完不懂來找我！

SQL專欄

SQL資料庫基礎知識彙總SQL資料庫進階知識彙總

作者：碼志

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

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 JOINLEFT 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 語句的圖檔，配合學習，風味更佳：

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)

——End——

背景回複關鍵字：1024，擷取一份精心整理的技術幹貨

圖解SQL裡的各種 JOIN，看完不懂來找我！

前言

約定

常用的 JOIN

1、INNER JOIN

3、RIGHT JOIN

4、FULL OUTER JOIN

小結

延伸用法

1、LEFT JOIN EXCLUDING INNER JOIN

2、RIGHT JOIN EXCLUDING INNER JOIN

3、FULL OUTER JOIN EXCLUDING INNER JOIN

總結

更多的 JOIN

1、CROSS JOIN

2、SELF JOIN

繼續閱讀

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

JAVA高效程式設計指南

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

關于SQL語言

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

詳解STM32單片機的堆棧

mysql使用source指令導入.sql檔案

sqlServer根據經緯查距離