Mysq中JOIN有什么用

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

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

0 索引

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

1 概述

一個完整的SQL語句中會被拆分成多個子句，子句的執(zhí)行過程中會產生虛擬表(vt)，但是結果只返回最后一張?zhí)摂M表。從這個思路出發(fā)，我們試著理解一下JOIN查詢的執(zhí)行過程并解答一些常見的問題。
如果之前對不同JOIN的執(zhí)行結果沒有概念，可以結合這篇文章往下看

2 JOIN的執(zhí)行順序

以下是JOIN查詢的通用結構

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

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

FROM:對左右兩張表執(zhí)行笛卡爾積，產生第一張表vt1。行數(shù)為n*m（n為左表的行數(shù)，m為右表的行數(shù)
ON:根據(jù)ON的條件逐行篩選vt1，將結果插入vt2中
JOIN:添加外部行，如果指定了LEFT JOIN(LEFT OUTER JOIN)，則先遍歷一遍左表的每一行，其中不在vt2的行會被插入到vt2，該行的剩余字段將被填充為NULL，形成vt3；如果指定了RIGHT JOIN也是同理。但如果指定的是INNER JOIN，則不會添加外部行，上述插入過程被忽略，vt2=vt3（所以INNER JOIN的過濾條件放在ON或WHERE里執(zhí)行結果是沒有區(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作為最終結果返回給客戶端

介紹完聯(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;

他會返回如下結果：

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

所以當你糾結使用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即最終結果：

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，得的最終結果：

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里，結果都是一樣的，因為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;

返回結果都是:

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

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

當前題目：Mysq中JOIN有什么用
轉載來源：http://muchs.cn/article24/phdoce.html

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

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

猜你還喜歡下面的內容