Mysq中JOIN有什么用

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

定制網(wǎng)站可以根據(jù)自己的需求進行定制，成都網(wǎng)站建設、網(wǎng)站設計構(gòu)思過程中功能建設理應排到主要部位公司成都網(wǎng)站建設、網(wǎng)站設計的運用實際效果公司網(wǎng)站制作網(wǎng)站建立與制做的實際意義

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

隨便導入一些數(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...: 返回左表的所有行，若某些行在右表里沒有相對應的匹配行，則將右表的列在新表中置為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 |
+--------+------+--------+-------+

因為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 |
+--------+------+--------+-------+

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

當前題目：Mysq中JOIN有什么用
轉(zhuǎn)載來源：http://bm7419.com/article24/phdoce.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站制作、自適應網(wǎng)站、網(wǎng)站建設、網(wǎng)頁設計公司、網(wǎng)站改版、外貿(mào)建站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

成都定制網(wǎng)站建設