歡迎光臨
每天分享高質量文章

詳解 MYSQL JOIN

(點選上方藍字,快速關註我們)


來源:llinvokerl

segmentfault.com/a/1190000015572505


0 索引

  • JOIN陳述句的執行順序

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

  • ONWHERE的區別

1 概述

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

如果之前對不同JOIN的執行結果沒有概念,可以結合這篇文章: https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins往下看。

2 JOIN的執行順序

以下是JOIN查詢的通用結構:

  1. SELECT

  2.  FROM

  3.     JOIN

  4.      ON

  5.        WHERE

它的執行順序如下(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的過濾條件放在ONWHERE裡 執行結果是沒有區別的,下文會細說)

  • WHERE:對vt3進行條件過濾,滿足條件的行被輸出到vt4

  • SELECT:取出vt4的指定欄位到vt5

下麵用一個例子介紹一下上述聯表的過程(這個例子不是個好的實踐,只是為了說明join語法)。

3 舉例

建立一個使用者資訊表:

  1. CREATE TABLE `user_info` (

  2.  `userid` int(11) NOT NULL,

  3.  `name` varchar(255) NOT NULL,

  4.  UNIQUE `userid` (`userid`)

  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

再建立一個使用者餘額表:

  1. CREATE TABLE `user_account` (

  2.  `userid` int(11) NOT NULL,

  3.  `money` bigint(20) NOT NULL,

  4. UNIQUE `userid` (`userid`)

  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

隨便匯入一些資料:

  1. select * from user_info;

  2. +--------+------+

  3. | userid | name |

  4. +--------+------+

  5. |   1001 | x    |

  6. |   1002 | y    |

  7. |   1003 | z    |

  8. |   1004 | a    |

  9. |   1005 | b    |

  10. |   1006 | c    |

  11. |   1007 | d    |

  12. |   1008 | e    |

  13. +--------+------+

  14. 8 rows in set (0.00 sec)

  15. select * from user_account;

  16. +--------+-------+

  17. | userid | money |

  18. +--------+-------+

  19. |   1001 |    22 |

  20. |   1002 |    30 |

  21. |   1003 |     8 |

  22. |   1009 |    11 |

  23. +--------+-------+

  24. 4 rows in set (0.00 sec)

一共8個使用者有使用者名稱,4個使用者的賬戶有餘額。

取出userid為1003的使用者姓名和餘額,SQL如下

  1. SELECT i.name, a.money

  2.  FROM user_info as i

  3.    LEFT JOIN user_account as a

  4.      ON i.userid = a.userid

  5.        WHERE a.userid = 1003;

第一步:執行FROM子句對兩張表進行笛卡爾積操作

笛卡爾積操作後會傳回兩張表中所有行的組合,左表userinfo有8行,右表useraccount有4行,生成的虛擬表vt1就是8*4=32行:

  1. SELECT * FROM user_info as i LEFT JOIN user_account as a ON 1;

  2. +--------+------+--------+-------+

  3. | userid | name | userid | money |

  4. +--------+------+--------+-------+

  5. |   1001 | x    |   1001 |    22 |

  6. |   1002 | y    |   1001 |    22 |

  7. |   1003 | z    |   1001 |    22 |

  8. |   1004 | a    |   1001 |    22 |

  9. |   1005 | b    |   1001 |    22 |

  10. |   1006 | c    |   1001 |    22 |

  11. |   1007 | d    |   1001 |    22 |

  12. |   1008 | e    |   1001 |    22 |

  13. |   1001 | x    |   1002 |    30 |

  14. |   1002 | y    |   1002 |    30 |

  15. |   1003 | z    |   1002 |    30 |

  16. |   1004 | a    |   1002 |    30 |

  17. |   1005 | b    |   1002 |    30 |

  18. |   1006 | c    |   1002 |    30 |

  19. |   1007 | d    |   1002 |    30 |

  20. |   1008 | e    |   1002 |    30 |

  21. |   1001 | x    |   1003 |     8 |

  22. |   1002 | y    |   1003 |     8 |

  23. |   1003 | z    |   1003 |     8 |

  24. |   1004 | a    |   1003 |     8 |

  25. |   1005 | b    |   1003 |     8 |

  26. |   1006 | c    |   1003 |     8 |

  27. |   1007 | d    |   1003 |     8 |

  28. |   1008 | e    |   1003 |     8 |

  29. |   1001 | x    |   1009 |    11 |

  30. |   1002 | y    |   1009 |    11 |

  31. |   1003 | z    |   1009 |    11 |

  32. |   1004 | a    |   1009 |    11 |

  33. |   1005 | b    |   1009 |    11 |

  34. |   1006 | c    |   1009 |    11 |

  35. |   1007 | d    |   1009 |    11 |

  36. |   1008 | e    |   1009 |    11 |

  37. +--------+------+--------+-------+

  38. 32 rows in set (0.00 sec)

第二步:執行ON子句過濾掉不滿足條件的行

ON i.userid = a.userid 過濾之後vt2如下:

  1. +--------+------+--------+-------+

  2. | userid | name | userid | money |

  3. +--------+------+--------+-------+

  4. |   1001 | x    |   1001 |    22 |

  5. |   1002 | y    |   1002 |    30 |

  6. |   1003 | z    |   1003 |     8 |

  7. +--------+------+--------+-------+

第三步:JOIN 新增外部行

LEFT JOIN會將左表未出現在vt2的行插入進vt2,每一行的剩餘欄位將被填充為NULL,RIGHT JOIN同理。

本例中用的是LEFT JOIN,所以會將左表user_info剩下的行都添上 生成表vt3:

  1. +--------+------+--------+-------+

  2. | userid | name | userid | money |

  3. +--------+------+--------+-------+

  4. |   1001 | x    |   1001 |    22 |

  5. |   1002 | y    |   1002 |    30 |

  6. |   1003 | z    |   1003 |     8 |

  7. |   1004 | a    |   NULL |  NULL |

  8. |   1005 | b    |   NULL |  NULL |

  9. |   1006 | c    |   NULL |  NULL |

  10. |   1007 | d    |   NULL |  NULL |

  11. |   1008 | e    |   NULL |  NULL |

  12. +--------+------+--------+-------+

第四步:WHERE條件過濾

WHERE a.userid = 1003 生成表vt4:

  1. +--------+------+--------+-------+

  2. | userid | name | userid | money |

  3. +--------+------+--------+-------+

  4. |   1003 | z    |   1003 |     8 |

  5. +--------+------+--------+-------+

第五步:SELECT

SELECT i.name, a.money 生成vt5:

  1. +------+-------+

  2. | name | money |

  3. +------+-------+

  4. | z    |     8 |

  5. +------+-------+

虛擬表vt5作為最終結果傳回給客戶端。

介紹完聯表的過程之後,我們看看常用JOIN的區別。

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

  • INNER JOIN...ON...: 傳回 左右表互相匹配的所有行(因為只執行上文的第二步ON過濾,不執行第三步 新增外部行)

  • LEFT JOIN...ON...: 傳回左表的所有行,若某些行在右表裡沒有相對應的匹配行,則將右表的列在新表中置為NULL

  • RIGHT JOIN...ON...: 傳回右表的所有行,若某些行在左表裡沒有相對應的匹配行,則將左表的列在新表中置為NULL

INNER JOIN

拿上文的第三步新增外部行來舉例,若LEFT JOIN替換成INNER JOIN,則會跳過這一步,生成的表vt3與vt2一模一樣:

  1. +--------+------+--------+-------+

  2. | userid | name | userid | money |

  3. +--------+------+--------+-------+

  4. |   1001 | x    |   1001 |    22 |

  5. |   1002 | y    |   1002 |    30 |

  6. |   1003 | z    |   1003 |     8 |

  7. +--------+------+--------+-------+

RIGHT JOIN

LEFT JOIN替換成RIGHT JOIN,則生成的表vt3如下:

  1. +--------+------+--------+-------+

  2. | userid | name | userid | money |

  3. +--------+------+--------+-------+

  4. |   1001 | x    |   1001 |    22 |

  5. |   1002 | y    |   1002 |    30 |

  6. |   1003 | z    |   1003 |     8 |

  7. |   NULL | NULL |   1009 |    11 |

  8. +--------+------+--------+-------+

因為useraccount(右表)裡存在userid=1009這一行,而userinfo(左表)裡卻找不到這一行的記錄,所以會在第三步插入以下一行:

  1. |   NULL | NULL |   1009 |    11 |

FULL JOIN

上文取用的文章中提到了標準SQL定義的FULL JOIN,這在mysql裡是不支援的,不過我們可以透過LEFT JOIN + UNION + RIGHT JOIN 來實現FULL JOIN

  1. SELECT *

  2.  FROM user_info as i

  3.    RIGHT JOIN user_account as a

  4.      ON a.userid=i.userid

  5. union

  6. SELECT *

  7.  FROM user_info as i

  8.    LEFT JOIN user_account as a

  9.      ON a.userid=i.userid;

他會傳回如下結果:

  1. +--------+------+--------+-------+

  2. | userid | name | userid | money |

  3. +--------+------+--------+-------+

  4. |   1001 | x    |   1001 |    22 |

  5. |   1002 | y    |   1002 |    30 |

  6. |   1003 | z    |   1003 |     8 |

  7. |   NULL | NULL |   1009 |    11 |

  8. |   1004 | a    |   NULL |  NULL |

  9. |   1005 | b    |   NULL |  NULL |

  10. |   1006 | c    |   NULL |  NULL |

  11. |   1007 | d    |   NULL |  NULL |

  12. |   1008 | e    |   NULL |  NULL |

  13. +--------+------+--------+-------+

ps:其實我們從語意上就能看出LEFT JOINRIGHT 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的區別

上文把JOIN的執行順序瞭解清楚之後,ON和WHERE的區別也就很好理解了。

舉例說明:

  1. SELECT *

  2.  FROM user_info as i

  3.    LEFT JOIN user_account as a

  4.      ON i.userid = a.userid and i.userid = 1003;

  1. SELECT *

  2.  FROM user_info as i

  3.    LEFT JOIN user_account as a

  4.      ON i.userid = a.userid where i.userid = 1003;

第一種情況LEFT JOIN在執行完第二步ON子句後,篩選出滿足i.userid = a.userid and i.userid = 1003的行,生成表vt2,然後執行第三步JOIN子句,將外部行新增進虛擬表生成vt3即最終結果:

  1. vt2:

  2. +--------+------+--------+-------+

  3. | userid | name | userid | money |

  4. +--------+------+--------+-------+

  5. |   1003 | z    |   1003 |     8 |

  6. +--------+------+--------+-------+

  7. vt3:

  8. +--------+------+--------+-------+

  9. | userid | name | userid | money |

  10. +--------+------+--------+-------+

  11. |   1001 | x    |   NULL |  NULL |

  12. |   1002 | y    |   NULL |  NULL |

  13. |   1003 | z    |   1003 |     8 |

  14. |   1004 | a    |   NULL |  NULL |

  15. |   1005 | b    |   NULL |  NULL |

  16. |   1006 | c    |   NULL |  NULL |

  17. |   1007 | d    |   NULL |  NULL |

  18. |   1008 | e    |   NULL |  NULL |

  19. +--------+------+--------+-------+

而第二種情況LEFT JOIN在執行完第二步ON子句後,篩選出滿足i.userid = a.userid的行,生成表vt2;再執行第三步JOIN子句新增外部行生成表vt3;然後執行第四步WHERE子句,再對vt3表進行過濾生成vt4,得的最終結果:

  1. vt2:

  2. +--------+------+--------+-------+

  3. | userid | name | userid | money |

  4. +--------+------+--------+-------+

  5. |   1001 | x    |   1001 |    22 |

  6. |   1002 | y    |   1002 |    30 |

  7. |   1003 | z    |   1003 |     8 |

  8. +--------+------+--------+-------+

  9. vt3:

  10. +--------+------+--------+-------+

  11. | userid | name | userid | money |

  12. +--------+------+--------+-------+

  13. |   1001 | x    |   1001 |    22 |

  14. |   1002 | y    |   1002 |    30 |

  15. |   1003 | z    |   1003 |     8 |

  16. |   1004 | a    |   NULL |  NULL |

  17. |   1005 | b    |   NULL |  NULL |

  18. |   1006 | c    |   NULL |  NULL |

  19. |   1007 | d    |   NULL |  NULL |

  20. |   1008 | e    |   NULL |  NULL |

  21. +--------+------+--------+-------+

  22. vt4:

  23. +--------+------+--------+-------+

  24. | userid | name | userid | money |

  25. +--------+------+--------+-------+

  26. |   1003 | z    |   1003 |     8 |

  27. +--------+------+--------+-------+

如果將上例的LEFT JOIN替換成INNER JOIN,不論將條件過濾放到ON還是WHERE裡,結果都是一樣的,因為INNER JOIN不會執行第三步新增外部行

  1. SELECT *

  2.  FROM user_info as i

  3.    INNER JOIN user_account as a

  4.      ON i.userid = a.userid and i.userid = 1003;

  1. SELECT *

  2.  FROM user_info as i

  3.    INNER JOIN user_account as a

  4.      ON i.userid = a.userid where i.userid = 1003;

傳回結果都是:

  1. +--------+------+--------+-------+

  2. | userid | name | userid | money |

  3. +--------+------+--------+-------+

  4. |   1003 | z    |   1003 |     8 |

  5. +--------+------+--------+-------+

參考資料

  • 《MySQL技術內幕:SQL程式設計》

  • SQL Joins - W3Schools

  • sql - What is the difference between “INNER JOIN” and “OUTER JOIN”?

  • MySQL :: MySQL 8.0 Reference Manual :: 13.2.10.2 JOIN Syntax

  • Visual Representation of SQL Joins

  • Join (SQL) - Wikipedia

【關於投稿】


如果大家有原創好文投稿,請直接給公號傳送留言。


① 留言格式:
【投稿】+《 文章標題》+ 文章連結

② 示例:
【投稿】
《不要自稱是程式員,我十多年的 IT 職場總結》:http://blog.jobbole.com/94148/


③ 最後請附上您的個人簡介哈~



看完本文有收穫?請轉發分享給更多人

關註「資料分析與開發」,提升資料技能

贊(0)

分享創造快樂