Mysq中JOIN有什么用

139次閱讀

共計 6790 個字符，預計需要花費 17 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章給大家分享的是有關 Mysq 中 JOIN 有什么用的內容。丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考。一起跟隨丸趣 TV 小編過來看看吧。

0 索引 JOIN 語句的執行順序 INNER/LEFT/RIGHT/FULL JOIN 的區別 ON 和 WHERE 的區別 1 概述

一個完整的 SQL 語句中會被拆分成多個子句，子句的執行過程中會產生虛擬表 (vt)，但是結果只返回最后一張虛擬表。從這個思路出發，我們試著理解一下 JOIN 查詢的執行過程并解答一些常見的問題。
如果之前對不同 JOIN 的執行結果沒有概念，可以結合這篇文章往下看

2 JOIN 的執行順序

以下是 JOIN 查詢的通用結構

SELECT row_list 
 FROM left_table 
 inner|left|right JOIN right_table 
 ON join condition 
 WHERE where_condition

它的執行順序如下(SQL 語句里第一個被執行的總是 FROM 子句)：

FROM: 對左右兩張表執行笛卡爾積，產生第一張表 vt1。行數為 n *m（n 為左表的行數，m 為右表的行數 ON: 根據 ON 的條件逐行篩選 vt1，將結果插入 vt2 中 JOIN: 添加外部行，如果指定了 LEFT JOIN(LEFT OUTER JOIN)，則先遍歷一遍左表的每一行，其中不在 vt2 的行會被插入到 vt2，該行的剩余字段將被填充為 NULL，形成 vt3；如果指定了 RIGHT JOIN 也是同理。但如果指定的是 INNER JOIN，則不會添加外部行，上述插入過程被忽略，vt2=vt3（所以 INNER JOIN 的過濾條件放在 ON 或 WHERE 里執行結果是沒有區別的，下文會細說）WHERE: 對 vt3 進行條件過濾，滿足條件的行被輸出到 vt4SELECT: 取出 vt4 的指定字段到 vt5

下面用一個例子介紹一下上述聯表的過程（這個例子不是個好的實踐，只是為了說明 join 語法）

3 舉例

創建一個用戶信息表：

CREATE TABLE `user_info` (`userid` int(11) NOT NULL,
 `name` varchar(255) NOT NULL,
 UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

再創建一個用戶余額表：

CREATE TABLE `user_account` (`userid` int(11) NOT NULL,
 `money` bigint(20) NOT NULL,
 UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

隨便導入一些數據：

select * from user_info;
+--------+------+
| userid | name |
+--------+------+
| 1001 | x |
| 1002 | y |
| 1003 | z |
| 1004 | a |
| 1005 | b |
| 1006 | c |
| 1007 | d |
| 1008 | e |
+--------+------+
8 rows in set (0.00 sec)
select * from user_account;
+--------+-------+
| userid | money |
+--------+-------+
| 1001 | 22 |
| 1002 | 30 |
| 1003 | 8 |
| 1009 | 11 |
+--------+-------+
4 rows in set (0.00 sec)

一共 8 個用戶有用戶名，4 個用戶的賬戶有余額。
取出 userid 為 1003 的用戶姓名和余額，SQL 如下：

SELECT i.name, a.money 
 FROM user_info as i 
 LEFT JOIN user_account as a 
 ON i.userid = a.userid 
 WHERE a.userid = 1003;

第一步：執行 FROM 子句對兩張表進行笛卡爾積操作

笛卡爾積操作后會返回兩張表中所有行的組合，左表 user_info 有 8 行，右表 user_account 有 4 行，生成的虛擬表 vt1 就是 8 *4=32 行：

SELECT * FROM user_info as i LEFT JOIN user_account as a ON 1;
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1001 | 22 |
| 1003 | z | 1001 | 22 |
| 1004 | a | 1001 | 22 |
| 1005 | b | 1001 | 22 |
| 1006 | c | 1001 | 22 |
| 1007 | d | 1001 | 22 |
| 1008 | e | 1001 | 22 |
| 1001 | x | 1002 | 30 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1002 | 30 |
| 1004 | a | 1002 | 30 |
| 1005 | b | 1002 | 30 |
| 1006 | c | 1002 | 30 |
| 1007 | d | 1002 | 30 |
| 1008 | e | 1002 | 30 |
| 1001 | x | 1003 | 8 |
| 1002 | y | 1003 | 8 |
| 1003 | z | 1003 | 8 |
| 1004 | a | 1003 | 8 |
| 1005 | b | 1003 | 8 |
| 1006 | c | 1003 | 8 |
| 1007 | d | 1003 | 8 |
| 1008 | e | 1003 | 8 |
| 1001 | x | 1009 | 11 |
| 1002 | y | 1009 | 11 |
| 1003 | z | 1009 | 11 |
| 1004 | a | 1009 | 11 |
| 1005 | b | 1009 | 11 |
| 1006 | c | 1009 | 11 |
| 1007 | d | 1009 | 11 |
| 1008 | e | 1009 | 11 |
+--------+------+--------+-------+
32 rows in set (0.00 sec)

第二步：執行 ON 子句過濾掉不滿足條件的行

ON i.userid = a.userid 過濾之后 vt2 如下：

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+

第三步：JOIN 添加外部行

LEFT JOIN 會將左表未出現在 vt2 的行插入進 vt2，每一行的剩余字段將被填充為 NULL，RIGHT JOIN 同理
本例中用的是 LEFT JOIN，所以會將左表 user_info 剩下的行都添上生成表 vt3：

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1003 | 8 |
| 1004 | a | NULL | NULL |
| 1005 | b | NULL | NULL |
| 1006 | c | NULL | NULL |
| 1007 | d | NULL | NULL |
| 1008 | e | NULL | NULL |
+--------+------+--------+-------+

第四步：WHERE 條件過濾

WHERE a.userid = 1003 生成表 vt4：

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+

第五步：SELECT

SELECT i.name, a.money 生成 vt5：

+------+-------+
| name | money |
+------+-------+
| z | 8 |
+------+-------+

虛擬表 vt5 作為最終結果返回給客戶端

介紹完聯表的過程之后，我們看看常用 JOIN 的區別

4 INNER/LEFT/RIGHT/FULL JOIN 的區別 INNER JOIN…ON…: 返回左右表互相匹配的所有行（因為只執行上文的第二步 ON 過濾，不執行第三步添加外部行）LEFT JOIN…ON…: 返回左表的所有行，若某些行在右表里沒有相對應的匹配行，則將右表的列在新表中置為 NULLRIGHT JOIN…ON…: 返回右表的所有行，若某些行在左表里沒有相對應的匹配行，則將左表的列在新表中置為 NULLINNER JOIN

拿上文的第三步添加外部行來舉例，若 LEFT JOIN 替換成 INNER JOIN，則會跳過這一步，生成的表 vt3 與 vt2 一模一樣：

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+

RIGHT JOIN

若 LEFT JOIN 替換成 RIGHT JOIN，則生成的表 vt3 如下：

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1003 | 8 |
| NULL | NULL | 1009 | 11 |
+--------+------+--------+-------+

因為 user_account（右表）里存在 userid=1009 這一行，而 user_info（左表）里卻找不到這一行的記錄，所以會在第三步插入以下一行：

| NULL | NULL | 1009 | 11 |

FULL JOIN

上文引用的文章中提到了標準 SQL 定義的 FULL JOIN，這在 mysql 里是不支持的，不過我們可以通過 LEFT JOIN + UNION + RIGHT JOIN 來實現 FULL JOIN：

SELECT * 
 FROM user_info as i 
 RIGHT JOIN user_account as a 
 ON a.userid=i.userid
union 
SELECT * 
 FROM user_info as i 
 LEFT JOIN user_account as a 
 ON a.userid=i.userid;

他會返回如下結果：

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1003 | 8 |
| NULL | NULL | 1009 | 11 |
| 1004 | a | NULL | NULL |
| 1005 | b | NULL | NULL |
| 1006 | c | NULL | NULL |
| 1007 | d | NULL | NULL |
| 1008 | e | NULL | NULL |
+--------+------+--------+-------+

ps：其實我們從語義上就能看出 LEFT JOIN 和 RIGHT JOIN 沒什么差別，兩者的結果差異取決于左右表的放置順序，以下內容摘自 mysql 官方文檔：

RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.

所以當你糾結使用 LEFT JOIN 還是 RIGHT JOIN 時，盡可能只使用 LEFT JOIN 吧

5 ON 和 WHERE 的區別

上文把 JOIN 的執行順序了解清楚之后，ON 和 WHERE 的區別也就很好理解了。
舉例說明:

SELECT * 
 FROM user_info as i
 LEFT JOIN user_account as a
 ON i.userid = a.userid and i.userid = 1003;

SELECT * 
 FROM user_info as i
 LEFT JOIN user_account as a
 ON i.userid = a.userid where i.userid = 1003;

第一種情況 LEFT JOIN 在執行完第二步 ON 子句后，篩選出滿足 i.userid = a.userid and i.userid = 1003 的行，生成表 vt2，然后執行第三步 JOIN 子句，將外部行添加進虛擬表生成 vt3 即最終結果：

vt2:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | NULL | NULL |
| 1002 | y | NULL | NULL |
| 1003 | z | 1003 | 8 |
| 1004 | a | NULL | NULL |
| 1005 | b | NULL | NULL |
| 1006 | c | NULL | NULL |
| 1007 | d | NULL | NULL |
| 1008 | e | NULL | NULL |
+--------+------+--------+-------+

而第二種情況 LEFT JOIN 在執行完第二步 ON 子句后，篩選出滿足 i.userid = a.userid 的行，生成表 vt2；再執行第三步 JOIN 子句添加外部行生成表 vt3；然后執行第四步 WHERE 子句，再對 vt3 表進行過濾生成 vt4，得的最終結果：

vt2:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1003 | 8 |
| 1004 | a | NULL | NULL |
| 1005 | b | NULL | NULL |
| 1006 | c | NULL | NULL |
| 1007 | d | NULL | NULL |
| 1008 | e | NULL | NULL |
+--------+------+--------+-------+
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+

如果將上例的 LEFT JOIN 替換成 INNER JOIN，不論將條件過濾放到 ON 還是 WHERE 里，結果都是一樣的，因為 INNER JOIN 不會執行第三步添加外部行

SELECT * 
 FROM user_info as i
 INNER JOIN user_account as a
 ON i.userid = a.userid and i.userid = 1003;

SELECT * 
 FROM user_info as i
 INNER JOIN user_account as a
 ON i.userid = a.userid where i.userid = 1003;

返回結果都是:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+

感謝各位的閱讀！關于 Mysq 中 JOIN 有什么用就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！

向 AI 問一下細節

丸趣 TV 網 – 提供最優質的資源集合！

正文完