歡迎光臨
每天分享高質量文章
當前位置：知識星球 > 後端 > 正文

圖解 SQL 裡的各種 JOIN

2019-01-28 分類：後端 閱讀(442) 評論(0)


來源：碼志   連結：

https://mazhuang.org/2017/09/11/joins-in-sql/

  • 前言
  • 約定
  • 常用的 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）中能關聯起來的資料連線後傳回。

文氏圖：

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 的別名，下同。

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)

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)

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 的種類和概念了，看一下它們的合影：

小結

有沒有感覺少了些什麼，學數學集合時完全不止這幾種情況？確實如此，繼續看。

延伸用法

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)

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)

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 查詢了。七種用法的全家福：

SQL JOINS

看著它們，我彷彿回到了當年學數學，求交集並集的時代……

順帶張貼一下 C.L. Moffatt 帶 SQL 陳述句的圖片，配合學習，風味更佳：

SQL JOINS

更新：更多的 JOIN

除以上幾種外，還有更多的 JOIN 用法，比如 CROSS JOIN（迪卡爾集）、SELF JOIN，可以參考 SQL JOINS Slide Presentation 學習。

CROSS JOIN

傳回左表與右表之間符合條件的記錄的迪卡爾集。

圖示：

CORSS 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)

補充說明

  1. 文中的圖使用 Keynote 繪製；
  2. 個人的體會是 SQL 裡的 JOIN 查詢與數學裡的求交集、並集等很像；
  3. SQLite 不支援 RIGHT JOIN 和 FULL OUTER JOIN，可以使用 LEFT JOIN 和 UNION 來達到相同的效果；
  4. MySQL 不支援 FULL OUTER JOIN，可以使用 LEFT JOIN 和 UNION 來達到相同的效果；

假如你對我的文章感興趣，可以關註我的微信公眾號 isprogrammer 隨時閱讀更多內容。

參考

  • Visual Representation of SQL Joins
  • How to do a FULL OUTER JOIN in MySQL?
  • SQL JOINS Slide Presentation
  • SQL Self Join


贊(0)
標籤：

相關推薦

熱門標籤

iOS (11238)微軟 (4955)Linux (4274)安全 (4180)Python (4161)效能 (3165)運維 (2774)最佳化 (2419).NET (2262)Google (2136)機器學習 (1795)併發 (1613)分散式 (1559)叢集 (1240)SQL (1174)Mysql (1060)區塊鏈 (1017)Docker (977)微服務 (922)面試 (919)Apache (743)NLP (725)Redis (719)Android (668)Git (640)架構師 (632)Nginx (630)Facebook (599)JVM (595)爬蟲 (476)

熱門文章

分享創造快樂