mysql視頻教程欄目詳解join

相關(guān)免費(fèi)學(xué)習(xí)推薦：mysql視頻教程

0 索引

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

1 概述

一個(gè)完整的SQL語(yǔ)句中會(huì)被拆分成多個(gè)子句，子句的執(zhí)行過(guò)程中會(huì)產(chǎn)生虛擬表(vt)，但是結(jié)果只返回最后一張?zhí)摂M表。從這個(gè)思路出發(fā)，我們?cè)囍斫庖幌翵OIN查詢的執(zhí)行過(guò)程并解答一些常見(jiàn)的問(wèn)題。
如果之前對(duì)不同JOIN的執(zhí)行結(jié)果沒(méi)有概念，可以結(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ì)添加外部行，上述插入過(guò)程被忽略，vt2=vt3（所以INNER JOIN的過(guò)濾條件放在ON或WHERE里 執(zhí)行結(jié)果是沒(méi)有區(qū)別的，下文會(huì)細(xì)說(shuō)）
• WHERE:對(duì)vt3進(jìn)行條件過(guò)濾，滿足條件的行被輸出到vt4
• SELECT:取出vt4的指定字段到vt5

下面用一個(gè)例子介紹一下上述聯(lián)表的過(guò)程（這個(gè)例子不是個(gè)好的實(shí)踐，只是為了說(shuō)明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子句過(guò)濾掉不滿足條件的行

ON i.userid = a.userid 過(guò)濾之后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條件過(guò)濾

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)表的過(guò)程之后，我們看看常用JOIN的區(qū)別

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

• INNER JOIN…ON…: 返回 左右表互相匹配的所有行（因?yàn)橹粓?zhí)行上文的第二步ON過(guò)濾，不執(zhí)行第三步 添加外部行）
• LEFT JOIN…ON…: 返回左表的所有行，若某些行在右表里沒(méi)有相對(duì)應(yīng)的匹配行，則將右表的列在新表中置為NULL
• RIGHT JOIN…ON…: 返回右表的所有行，若某些行在左表里沒(méi)有相對(duì)應(yīng)的匹配行，則將左表的列在新表中置為NULL

INNER JOIN

拿上文的第三步添加外部行來(lái)舉例，若LEFT JOIN替換成INNER JOIN，則會(huì)跳過(guò)這一步，生成的表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里是不支持的，不過(guò)我們可以通過(guò)LEFT JOIN + UNION + RIGHT JOIN 來(lái)實(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沒(méi)什么差別，兩者的結(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ū)別也就很好理解了。
舉例說(shuō)明:

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)行過(guò)濾生成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，不論將條件過(guò)濾放到ON還是WHERE里，結(jié)果都是一樣的，因?yàn)?strong>INNER 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 | +--------+------+--------+-------+

想了解