MySQL LEFT JOIN/ INNER JOIN/RIGHT JOIN

沙漠行者發表於2019-04-19
MySql

概述

一個完整的SQL語句會被拆分成很多個子句,子句在執行過程中會產生多個臨時表(vt),但是結果只返回最後一張臨時表。從這個思路出發，我們試著理解一下JOIN查詢的執行過程並解答一些常見的問題。

JOIN 執行順序

JOIN查詢的通用結構:

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

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

  • FROM:對左右倆表執行笛卡爾積，產生第一個臨時表vt1,行數為n*m條,（n為左表的行數，m為右表的行數)。
  • ON：根據on條件對vt1表進行過濾，結構插入到vt2表。
  • JOIN：新增外部行。如果指定了LEFT JOIN(LEFT OUTER JOIN)，則先遍歷一遍左表的每一行，其中不在vt2的行會被插入到vt2，該行的剩餘欄位將被填充為NULL，形成vt3；如果指定了RIGHT JOIN也是同理。但如果指定的是INNER JOIN，則不會新增外部行，上述插入過程被忽略，vt2=vt3（所以INNER JOIN的過濾條件放在ON或WHERE裡 執行結果是沒有區別的，下文會細說）
  • WHERE,根據where條件對vt3表篩選，生成vt4表。
  • SELECT，取出vt4的指定欄位到vt5

舉例

建立一個使用者資訊表：

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

建立一個使用者餘額表:

CREATE TABLE `user_account` (
  `userid` int(11) NOT NULL,
  `money` bigint(20) NOT NULL,
 UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

匯入一些資料：

user_info:

|userid  | name |
| -------- | -------- |
| 10001   | x | 
| 10002   | y | 
| 10003   | z | 
| 10004   | a | 
| 10005   | b | 
| 10006   | c | 
| 10007   | d | 
| 10008   | e |

user_account:

| userid | money |
|  --------  |  --------  |
|   1001 |    22 |
|   1002 |    30 |
|   1003 |     8 |
|   1009 |    11 |

一共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;

第一步：執行FROM從句，對倆個表進行笛卡爾積操作。

笛卡爾積操作後會返回倆個表所有行對組合，左表user_info有8行，右表user_account有4行，生成的虛擬表vt1就是8*4=32行：

| 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 |

第二步，執行ON,把不滿足條件對行去掉，生成vt2表：

| userid | name | userid | money |
|  --------  |  --------  | --------  |  --------  |
|  1001  | x |  1001  |  22  |
|  1002  | y |  1002  |  30  |
|  1003  | z |  1003  |  8  |

第三步，執行JOIN，進行新增外部行。

LEFT JOIN遍歷左表user_info,把所有左表中沒有出現在vt2表中的行插入到vt2表中，每一行的剩餘欄位將會用NULL填充，生成vt3表。RIGHT JOIN也是同理。

| userid | name | userid | money |
|  --------  |  --------  | --------  |  --------  |
|  1001  | x |  1001  |  22  |
|  1002  | y |  1002  |  30  |
|  1003  | z |  1003  |  8  |
|  10004  | a | NULL | NULL |
|  10005  | b | NULL | NULL |
|  10006  | c | NULL | NULL |
|  10007  | d | NULL | NULL |
|  10008  | e | NULL | NULL |

第四步，執行WHERE條件 userid = 1003 篩選，生成vt4表。

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

第五步，執行SELECT 查詢具體欄位。生成vt5將作為結果返回。

 | name | money |
 |  --------   |  --------  |
 |  z |   8  |

INNER/LEFT/RIGHT/FULL JOIN的區別。

 INNER JOIN...ON...: 返回倆表關聯的所有行，不執行上面說的第三部JOIN新增外部行。
 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（左表）裡卻找不到這一行的記錄，所以會在第三步插入以下一行：

| userid | name | userid | money |
|  --------   |  --------  |   --------   |  --------  |
|   NULL | NULL |   1009 |    11 |

我們可以看出其實LEFT JOIN 和 RIGHT JOIN 沒有什麼區別，所以儘量使用LEFT JOIN。

ON和WHERE的區別

舉例說明：

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在執行完第二步ON子句後，篩選出滿足i.userid = a.userid and i.userid = 1003的行，生成表vt2，然後執行第三步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在執行完第二步ON子句後，篩選出滿足i.userid = a.userid的行，生成表vt2；再執行第三步JOIN子句新增外部行生成表vt3；然後執行第四步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不會執行第三步新增外部行。

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 |

原文地址

原文地址連結

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章