MySQL – JOIN 詳解-知識星球

作者：llinvokerl

來自：https://segmentfault.com/a/1190000015572505

0 索引

JOIN陳述句的執行順序
INNER/LEFT/RIGHT/FULL JOIN的區別
ON和WHERE的區別

1 概述

一個完整的SQL陳述句中會被拆分成多個子句，子句的執行過程中會產生虛擬表(vt)，但是結果只傳回最後一張虛擬表。從這個思路出發，我們試著理解一下JOIN查詢的執行過程並解答一些常見的問題。

如果之前對不同JOIN的執行結果沒有概念，可以結合這篇文章： https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins 往下看。

2 JOIN的執行順序

以下是JOIN查詢的通用結構：

SELECT  
  FROM  
     JOIN  
      ON  
        WHERE

它的執行順序如下(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進行條件過濾，滿足條件的行被輸出到vt4
SELECT:取出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子句對兩張表進行笛卡爾積操作
笛卡爾積操作後會傳回兩張表中所有行的組合，左表userinfo有8行，右表useraccount有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...: 傳回左表的所有行，若某些行在右表裡沒有相對應的匹配行，則將右表的列在新表中置為NULL


RIGHT JOIN...ON...: 傳回右表的所有行，若某些行在左表裡沒有相對應的匹配行，則將左表的列在新表中置為NULL


INNER 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 |
+--------+------+--------+-------+
因為useraccount（右表）裡存在userid=1009這一行，而userinfo（左表）裡卻找不到這一行的記錄，所以會在第三步插入以下一行：
|   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 |
+--------+------+--------+-------+
vt3:
+--------+------+--------+-------+
| 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 |
+--------+------+--------+-------+
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 |
+--------+------+--------+-------+
vt4:
+--------+------+--------+-------+
| 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 |
+--------+------+--------+-------+
參考資料


《MySQL技術內幕：SQL程式設計》


SQL Joins - W3Schools


sql - What is the difference between “INNER JOIN” and “OUTER JOIN”?


MySQL :: MySQL 8.0 Reference Manual :: 13.2.10.2 JOIN Syntax


Visual Representation of SQL Joins


Join (SQL) - Wikipedia




●編號374，輸入編號直達本文

●輸入m獲取文章目錄




推薦↓↓↓






Web開發







更多推薦《18個技術類公眾微信》

涵蓋：程式人生、演演算法與資料結構、駭客技術與網路安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。





llinvokerl


贊賞



長按二維碼向我轉賬


受蘋果公司新規定影響，微信 iOS 版的贊賞功能被關閉，可透過二維碼轉賬支援公眾號。



閱讀原文






            微信掃一掃
使用小程式 





即將開啟""小程式

取消

開啟

MySQL – JOIN 詳解

作者：llinvokerl

來自：https://segmentfault.com/a/1190000015572505

0 索引

1 概述

2 JOIN的執行順序

3 舉例

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

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

第三步：JOIN 新增外部行

第四步：WHERE條件過濾

第五步：SELECT

4 INNER/LEFT/RIGHT/FULL JOIN的區別

INNER JOIN

RIGHT JOIN

FULL JOIN

5 ON和WHERE的區別

參考資料

相關推薦

熱門標籤

熱門文章

分享創造快樂