歡迎光臨
每天分享高質量文章

圖解 SQL 里的各種 JOIN


來源:碼志   鏈接:

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)

分享創造快樂