對數(shù)據(jù)庫MySQL中的JOIN詳解

作者：llinvokerl 2018-07-30 15:00:05

一個完整的SQL語句中會被拆分成多個子句，子句的執(zhí)行過程中會產(chǎn)生虛擬表(vt)，但是結(jié)果只返回最后一張?zhí)摂M表。從這個思路出發(fā)，我們試著理解一下JOIN查詢的執(zhí)行過程并解答一些常見的問題。

0 索引

JOIN語句的執(zhí)行順序
INNER/LEFT/RIGHT/FULL JOIN的區(qū)別
ON和WHERE的區(qū)別

1 概述

一個完整的SQL語句中會被拆分成多個子句，子句的執(zhí)行過程中會產(chǎn)生虛擬表(vt)，但是結(jié)果只返回***一張?zhí)摂M表。從這個思路出發(fā)，我們試著理解一下JOIN查詢的執(zhí)行過程并解答一些常見的問題。

如果之前對不同JOIN的執(zhí)行結(jié)果沒有概念，可以結(jié)合這篇文章往下看

2 JOIN的執(zhí)行順序

以下是JOIN查詢的通用結(jié)構(gòu)

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

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

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

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

3 舉例

創(chuàng)建一個用戶信息表：

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

再創(chuàng)建一個用戶余額表：

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

隨便導(dǎo)入一些數(shù)據(jù)：

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;

***步：執(zhí)行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)

第二步：執(zhí)行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會將左表未出現(xiàn)在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作為最終結(jié)果返回給客戶端

介紹完聯(lián)表的過程之后，我們看看常用JOIN的區(qū)別

4 INNER/LEFT/RIGHT/FULL JOIN的區(qū)別

INNER JOIN...ON...: 返回左右表互相匹配的所有行（因為只執(zhí)行上文的第二步ON過濾，不執(zhí)行第三步添加外部行）
LEFT JOIN...ON...: 返回左表的所有行，若某些行在右表里沒有相對應(yīng)的匹配行，則將右表的列在新表中置為NULL
RIGHT JOIN...ON...: 返回右表的所有行，若某些行在左表里沒有相對應(yīng)的匹配行，則將左表的列在新表中置為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 |  
+--------+------+--------+-------+

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

| NULL | NULL | 1009 | 11 |

FULL JOIN

上文引用的文章中提到了標準SQL定義的FULL JOIN，這在mysql里是不支持的，不過我們可以通過LEFT JOIN + UNION + RIGHT JOIN 來實現(xiàn)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;

他會返回如下結(jié)果：

+--------+------+--------+-------+  
| 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沒什么差別，兩者的結(jié)果差異取決于左右表的放置順序，以下內(nèi)容摘自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.

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

5 ON和WHERE的區(qū)別

上文把JOIN的執(zhí)行順序了解清楚之后，ON和WHERE的區(qū)別也就很好理解了。

舉例說明:

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在執(zhí)行完第二步ON子句后，篩選出滿足i.userid = a.userid and i.userid = 1003的行，生成表vt2，然后執(zhí)行第三步JOIN子句，將外部行添加進虛擬表生成vt3即最終結(jié)果：

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在執(zhí)行完第二步ON子句后，篩選出滿足i.userid = a.userid的行，生成表vt2；再執(zhí)行第三步JOIN子句添加外部行生成表vt3；然后執(zhí)行第四步WHERE子句，再對vt3表進行過濾生成vt4，得的最終結(jié)果：

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里，結(jié)果都是一樣的，因為INNER JOIN不會執(zhí)行第三步添加外部行

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;

返回結(jié)果都是:

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

參考資料

《MySQL技術(shù)內(nèi)幕：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)

責任編輯：龐桂玉來源： segmentfault

成人免费xxxxx在线视频软件_久久精品久久久_亚洲国产精品久久久_天天色天天色_亚洲人成一区_欧美一级欧美三级在线观看