從業(yè)以來主要在做客戶端,用到的數(shù)據(jù)庫都是表結(jié)構(gòu)比較簡單的 SQLite,以我那還給老師一大半的 SQL 水平倒也能對付.現(xiàn)在偶爾需要到后臺的 SQL Server 里追查一些數(shù)據(jù)問題,就顯得有點捉襟見肘了,特別是各種 JOIN,有時候傻傻分不清楚,于是索性弄明白并做個記錄.

前言

在各種問答社區(qū)里談及 SQL 里的各種 JOIN 之間的區(qū)別時,最被廣為引用的是 CodeProject 上 C.L. Moffatt 的文章 Visual Representation of SQL Joins,他確實講得簡單明了,使用文氏圖來贊助理解,效果明顯.本文將沿用他的講解方式,稍有演繹,可以視為該文較為粗糙的中譯版.

約定

下文將使用兩個數(shù)據(jù)庫表 Table_A 和 Table_B 來進行示例講解,其結(jié)構(gòu)與數(shù)據(jù)分別如下：

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 一般被譯作內(nèi)連接.內(nèi)連接查詢能將左表(表 A)和右表(表 B)中能關(guān)聯(lián)起來的數(shù)據(jù)連接后返回.

文氏圖：

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 AINNER JOIN Table_B BON A.PK = B.PK;

查詢結(jié)果：

+------+------+---------+---------+| 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)中有沒有關(guān)聯(lián)的數(shù)據(jù).在右表中找到的關(guān)聯(lián)數(shù)據(jù)列也會被一起返回.

文氏圖：

LEFT 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.PK;

查詢結(jié)果：

+------+------+---------+---------+| 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)中有沒有關(guān)聯(lián)的數(shù)據(jù).在左表中找到的關(guān)聯(lián)數(shù)據(jù)列也會被一起返回.

文氏圖：

RIGHT 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.PK;

查詢結(jié)果：

+------+------+---------+---------+| 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.外連接查詢能返回左右表里的所有記錄,其中左右表里能關(guān)聯(lián)起來的記錄被連接后返回.

文氏圖：

FULL OUTER 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;

查詢結(jié)果：

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

注：我當(dāng)前示例使用的 MySQL 不支持 FULL OUTER JOIN.

應(yīng)當(dāng)返回的結(jié)果(使用 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)

小結(jié)

以上四種,就是 SQL 里常見 JOIN 的種類和概念了,看一下它們的合影：

有沒有感覺少了些什么,學(xué)數(shù)學(xué)集合時完全不止這幾種情況?確實如此,繼續(xù)看.

延伸用法

LEFT JOIN EXCLUDING INNER JOIN

返回左表有但右表沒有關(guān)聯(lián)數(shù)據(jù)的記錄集.

文氏圖：

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;

查詢結(jié)果：

+------+------+---------+---------+| 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

返回右表有但左表沒有關(guān)聯(lián)數(shù)據(jù)的記錄集.

文氏圖：

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;

查詢結(jié)果：

+------+------+---------+---------+| 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

返回左表和右表里沒有相互關(guān)聯(lián)的記錄集.

文氏圖：

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 在執(zhí)行該查詢時再次報錯.

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

應(yīng)當(dāng)返回的結(jié)果(用 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)

總結(jié)

以上七種用法基本上可以覆蓋各種 JOIN 查詢了.七種用法的百口福：

SQL JOINS

看著它們,我仿佛回到了當(dāng)年學(xué)數(shù)學(xué),求交集并集的時代……

順帶張貼一下 C.L. Moffatt 帶 SQL 語句的圖片,配合學(xué)習(xí),風(fēng)味更佳：

SQL JOINS

更新：更多的 JOIN

除以上幾種外,還有更多的 JOIN 用法,比如 CROSS JOIN(迪卡爾集)、SELF JOIN,可以參考 SQL JOINS Slide Presentation 學(xué)習(xí).

CROSS JOIN

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

圖示：

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

查詢結(jié)果：

+------+------+---------+---------+| 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 查詢的結(jié)果都可以用 CROSS JOIN 加條件模擬出來,比如 INNER JOIN 對應(yīng) CROSS JOIN ... WHERE A.PK = B.PK.

SELF JOIN

返回表與本身連接后符合條件的記錄,一般用在表里有一個字段是用主鍵作為外鍵的情況.

比如 Table_C 的結(jié)構(gòu)與數(shù)據(jù)如下：

+--------+----------+-------------+| 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.

示例查詢：

現(xiàn)在我們想查詢所有有主管的員工及其對應(yīng)的主管 ID 和姓名,就可以用 SELF JOIN 來實現(xiàn).

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;

查詢結(jié)果：

+--------+----------+-------------+---------------+| 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 查詢與數(shù)學(xué)里的求交集、并集等很像；

3. SQLite 不支持 RIGHT JOIN 和 FULL OUTER JOIN,可以使用 LEFT JOIN 和 UNION 來達到相同的效果；

4. MySQL 不支持 FULL OUTER JOIN,可以使用 LEFT JOIN 和 UNION 來達到相同的效果；

參考

• Visual Representation of SQL Joins

• How to do a FULL OUTER JOIN in MySQL?

• SQL JOINS Slide Presentation

• SQL Self Join

1、具有1-5工作經(jīng)驗的,面對目前流行的技術(shù)不知從何下手,需要突破技術(shù)瓶頸的可以加群.

2、在公司待久了,過得很安適,但跳槽時面試碰壁.需要在短時間內(nèi)進修、跳槽拿高薪的可以加群.

3、如果沒有工作經(jīng)驗,但基礎(chǔ)非常扎實,對java工作機制,常用設(shè)計思想,常用java開發(fā)框架掌握熟練的,可以加群.

4、覺得本身很牛B,一般需求都能搞定.但是所學(xué)的知識點沒有系統(tǒng)化,很難在技術(shù)領(lǐng)域繼續(xù)突破的可以加群.

5. 群號：高級架構(gòu)群 647631030備注好信息!

6.阿里Java高級架構(gòu)師直播講解知識點,分享知識,多年工作經(jīng)驗的梳理和總結(jié),帶著大家全面、科學(xué)地建立本身的技術(shù)體系和技術(shù)認(rèn)知!