圖解 SQL 里的各種 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)中能關聯起來的數據連接後返回。
文氏圖:
示例查詢:
SELECT A.PK AS A_PK, B.PK AS B_PK, A.Value AS A_Value, B.Value AS B_ValueFROM Table_A AINNER JOIN Table_B BON 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_ValueFROM Table_A ALEFT JOIN Table_B BON 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_ValueFROM Table_A ARIGHT JOIN Table_B BON 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_ValueFROM Table_A AFULL OUTER JOIN Table_B BON 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 BON 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_ValueFROM Table_A ALEFT JOIN Table_B BON A.PK = B.PKWHERE 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_ValueFROM Table_A ARIGHT JOIN Table_B BON A.PK = B.PKWHERE 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_ValueFROM Table_A AFULL OUTER JOIN Table_B BON A.PK = B.PKWHERE A.PK IS NULLOR 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 BON A.PK = B.PKWHERE A.PK IS NULLOR 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_ValueFROM Table_A ACROSS 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_NAMEFROM Table_C A, Table_C BWHERE 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 來達到相同的效果;
假如你對我的文章感興趣,可以關注我的微信公眾號 isprogrammer 隨時閱讀更多內容。
參考
- Visual Representation of SQL Joins
- How to do a FULL OUTER JOIN in MySQL?
- SQL JOINS Slide Presentation
- SQL Self Join
原始鏈接:圖解 SQL 里的各種 JOIN
推薦閱讀:
※有哪些值得推薦的 Kettle 的學習資料?
※現在資料庫的發展方向如何,在商業智能領域中(主要處理數據業務)作為一名 ETL 調度人員所需要的專業知識有哪些?
※Learn SQL | 基礎操作綜合練習