0 索引

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

1 概述

一個(gè)完整的SQL語(yǔ)句中會(huì)被拆分成多個(gè)子句，子句的執(zhí)行過程中會(huì)產(chǎn)生虛擬表(vt)，但是結(jié)果只返回***一張?zhí)摂M表。從這個(gè)思路出發(fā)，我們?cè)囍斫庖幌翵OIN查詢的執(zhí)行過程并解答一些常見的問題。

如果之前對(duì)不同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語(yǔ)句里***個(gè)被執(zhí)行的總是FROM子句)：

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

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

3 舉例

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

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

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

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個(gè)用戶有用戶名，4個(gè)用戶的賬戶有余額。

取出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子句對(duì)兩張表進(jìn)行笛卡爾積操作

笛卡爾積操作后會(huì)返回兩張表中所有行的組合，左表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會(huì)將左表未出現(xiàn)在vt2的行插入進(jìn)vt2，每一行的剩余字段將被填充為NULL，RIGHT JOIN同理

本例中用的是LEFT JOIN，所以會(huì)將左表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...: 返回 左右表互相匹配的所有行（因?yàn)橹粓?zhí)行上文的第二步ON過濾，不執(zhí)行第三步 添加外部行）
• LEFT JOIN...ON...: 返回左表的所有行，若某些行在右表里沒有相對(duì)應(yīng)的匹配行，則將右表的列在新表中置為NULL
• RIGHT JOIN...ON...: 返回右表的所有行，若某些行在左表里沒有相對(duì)應(yīng)的匹配行，則將左表的列在新表中置為NULL

INNER JOIN

拿上文的第三步添加外部行來舉例，若LEFT JOIN替換成INNER JOIN，則會(huì)跳過這一步，生成的表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 |  
+--------+------+--------+-------+ 

因?yàn)閡ser_account（右表）里存在userid=1009這一行，而user_info（左表）里卻找不到這一行的記錄，所以會(huì)在第三步插入以下一行：

| NULL | NULL | 1009 | 11 | 

FULL JOIN

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

他會(huì)返回如下結(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：其實(shí)我們從語(yǔ)義上就能看出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. 

所以當(dāng)你糾結(jié)使用LEFT JOIN還是RIGHT JOIN時(shí)，盡可能只使用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子句，將外部行添加進(jìn)虛擬表生成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子句，再對(duì)vt3表進(jìn)行過濾生成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é)果都是一樣的，因?yàn)镮NNER JOIN不會(huì)執(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)