Mysq中JOIN有什么用

發(fā)布時(shí)間：2020-12-07 10:37:09 來(lái)源：億速云閱讀：183 作者：小新欄目：MySQL數(shù)據(jù)庫(kù)

這篇文章給大家分享的是有關(guān)Mysq中JOIN有什么用的內(nèi)容。小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧。

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ò)程并解答一些常見的問(wèn)題。
如果之前對(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ì)添加外部行，上述插入過(guò)程被忽略，vt2=vt3（所以INNER JOIN的過(guò)濾條件放在ON或WHERE里執(zhí)行結(jié)果是沒有區(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...: 返回左表的所有行，若某些行在右表里沒有相對(duì)應(yīng)的匹配行，則將右表的列在新表中置為NULL
RIGHT JOIN...ON...: 返回右表的所有行，若某些行在左表里沒有相對(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沒什么差別，兩者的結(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 |
+--------+------+--------+-------+

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

向AI問(wèn)一下細(xì)節(jié)