Visual explanation of MySQL joins: Difference between revisions
Jump to navigation
Jump to search
m
Text replacement - "http://planetoid.info/images/" to "https://planetoid.info/images/"
m (→預覽輸出資料) |
m (Text replacement - "http://planetoid.info/images/" to "https://planetoid.info/images/") Tags: Mobile edit Mobile web edit |
||
| (30 intermediate revisions by one other user not shown) | |||
| Line 1: | Line 1: | ||
使用文氏圖 (Venn diagram) 視覺化解釋 MySQL Joins (RIGHT Join、LEFT Join、INNER Join、OUTER Join) 的差異 | |||
== 摘要 == | |||
# 狀況 1-1: 以 a 資料為主，再把 b 資料黏上: LEFT JOIN | |||
#* 如果 a 資料有，但是 b 資料沒有。 b 欄位顯示 null | |||
# 狀況 2: 交集 (set intersection) 的資料，同時存在於 a 和 b: INNER JOIN | |||
#* 如果 a 資料有，但是 b 資料沒有。 不會顯示 | |||
# 狀況 3-1: 相對差集 (set difference) 的資料，存在於 a，但是不存在於 b: LEFT JOIN 並且加上 b 資料 id 欄位值是 null 做查詢 | |||
# 狀況 4: 聯集 (set union) 的資料，a 和 b 的所有資料 | |||
#* 如果 a 資料有，但是 b 資料沒有。 b 欄位顯示 null | |||
# 狀況 5: 對稱差集 (set symmetric difference) 的資料，只存在於 a 或 b 其中一個 | |||
== 準備測試資料 == | == 準備測試資料 == | ||
兩個資料表的 id 欄位都是主鍵( [http://www.w3schools.com/sql/sql_primarykey.asp PRIMARY KEY] )。 | |||
<pre> | <pre> | ||
CREATE TABLE table_a (id int NOT NULL PRIMARY KEY, name varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL); | CREATE TABLE table_a (id int NOT NULL PRIMARY KEY, name varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL); | ||
| Line 26: | Line 37: | ||
=== 預覽測試資料 === | === 預覽測試資料 === | ||
兩個資料表都有的資料，以紅色標示: | |||
<table> | <table> | ||
<tr> | <tr> | ||
| Line 48: | Line 60: | ||
</table> | </table> | ||
== | == 狀況 1-1: 以 a 資料為主，再把 b 資料黏上 == | ||
{{exclaim}} 如果 a 資料比 b 資料的資料筆數少，將會以 a 資料筆數為主，部分 b 資料會遺失。 | |||
https://planetoid.info/images/Venn_diagrams_proposition_a.png | |||
MySQL 資料庫查詢: | |||
<pre> | |||
SELECT * FROM table_a | |||
LEFT JOIN table_b | |||
ON table_a.id = table_b.id; | |||
/*相同效果的別種寫法*/ | |||
SELECT * FROM table_a | |||
LEFT OUTER JOIN table_b | |||
ON table_a.id = table_b.id; | |||
</pre> | |||
=== 預覽輸出資料 === | |||
<pre> | |||
1|小一|1|女 | |||
3|小三|3|女 | |||
2|小二|NULL|NULL | |||
4|小四|NULL|NULL | |||
</pre> | |||
== 狀況 1-2: 以 b 資料為主，再把 a 資料黏上 == | |||
https://planetoid.info/images/Venn_diagrams_proposition_b.png | |||
MySQL 資料庫查詢: | |||
<pre> | |||
SELECT * FROM table_a | |||
RIGHT JOIN table_b | |||
ON table_a.id = table_b.id; | |||
/*相同效果的別種寫法*/ | |||
SELECT * FROM table_a | |||
RIGHT OUTER JOIN table_b | |||
ON table_a.id = table_b.id; | |||
</pre> | |||
=== 預覽輸出資料 === | |||
<pre> | |||
1|小一|1|女 | |||
3|小三|3|女 | |||
NULL|NULL|5|男 | |||
NULL|NULL|6|不明 | |||
</pre> | |||
== 狀況 2: 交集的資料，同時存在於 a 和 b == | |||
https://planetoid.info/images/Venn_diagrams_intersection_of_two_sets.png | |||
MySQL 資料庫查詢: | MySQL 資料庫查詢: | ||
| Line 61: | Line 121: | ||
SELECT a.*, b.* FROM table_a AS a NATURAL JOIN table_b AS b; | SELECT a.*, b.* FROM table_a AS a NATURAL JOIN table_b AS b; | ||
-- INNER JOIN 寫成 JOIN | |||
SELECT * FROM table_a | |||
JOIN table_b | |||
ON table_a.id = table_b.id; | |||
</pre> | </pre> | ||
| Line 69: | Line 134: | ||
</pre> | </pre> | ||
== | == 狀況 3-1: 相對差集的資料，存在於 a，但是不存在於 b == | ||
存在於資料表 table_a ，但是不存在於 table_b | 存在於資料表 table_a ，但是不存在於 table_b | ||
https://planetoid.info/images/Venn_diagrams_relative_complement_of_b_(right)_in_a_(left).png | |||
MySQL 資料庫查詢: | MySQL 資料庫查詢: | ||
| Line 87: | Line 152: | ||
4|小四|NULL|NULL | 4|小四|NULL|NULL | ||
</pre> | </pre> | ||
== | == 狀況 3-2: 相對差集的資料，存在於 b，但是不存在於 a == | ||
存在於資料表 table_b ，但是不存在於 table_a | 存在於資料表 table_b ，但是不存在於 table_a | ||
https://planetoid.info/images/Venn_diagrams_relative_complement_of_a_(left)_in_b_(right).png | |||
MySQL 資料庫查詢: | MySQL 資料庫查詢: | ||
| Line 106: | Line 171: | ||
</pre> | </pre> | ||
== | == 狀況 4: 聯集的資料，a 和 b 的所有資料 == | ||
https://planetoid.info/images/Venn_diagrams_union_of_two_sets.png | |||
MySQL 資料庫查詢 (相當於 SQL 的 FULL OUTER JOIN): | MySQL 資料庫查詢 (相當於 SQL 的 FULL OUTER JOIN): | ||
| Line 132: | Line 197: | ||
</pre> | </pre> | ||
== | == 狀況 5: 對稱差集的資料，只存在於 a 或 b 其中一個== | ||
對稱差集 (Symmetric difference) 或稱 [https://zh.wikipedia.org/zh-tw/%E9%80%BB%E8%BE%91%E5%BC%82%E6%88%96 邏輯異或] (exclusive or) 的資料，只存在於 table_a 或 table_b 其中一個 | |||
https://planetoid.info/images/Venn_diagrams_symmetric_difference_of_two.png | |||
MySQL 資料庫查詢: | MySQL 資料庫查詢: | ||
| Line 160: | Line 225: | ||
</pre> | </pre> | ||
參考資料 | == 參考資料 == | ||
* [http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ A Visual Explanation of SQL Joins] | * [http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ A Visual Explanation of SQL Joins] | ||
* [http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins sql - Difference between INNER and OUTER joins - Stack Overflow] | |||
* [http://blog.wu-boy.com/2009/01/mysqlleft-right-inner-outer-join-%E4%BD%BF%E7%94%A8%E6%96%B9%E6%B3%95/ (MySQL)left, right, inner, outer join 使用方法 - 小惡魔 - 電腦技術 - 工作筆記 - AppleBOY] | * [http://blog.wu-boy.com/2009/01/mysqlleft-right-inner-outer-join-%E4%BD%BF%E7%94%A8%E6%96%B9%E6%B3%95/ (MySQL)left, right, inner, outer join 使用方法 - 小惡魔 - 電腦技術 - 工作筆記 - AppleBOY] | ||
* [https://zh.wikipedia.org/wiki/Wikipedia:%E6%95%B0%E5%AD%A6%E4%B8%93%E9%A2%98/%E9%9B%86%E5%90%88%E8%AE%BA 維基百科:數學專題/集合論 - 維基百科，自由的百科全書] | * [https://zh.wikipedia.org/wiki/Wikipedia:%E6%95%B0%E5%AD%A6%E4%B8%93%E9%A2%98/%E9%9B%86%E5%90%88%E8%AE%BA 維基百科:數學專題/集合論 - 維基百科，自由的百科全書] | ||
* Steve Stedman (2015). [http://stevestedman.com/2015/03/mysql-join-types-poster/ MySQL JOIN Types Poster - Steve Stedman] {{access | date = 2017-02-21}} | |||
* [https://zh.wikipedia.org/wiki/%E8%BF%9E%E6%8E%A5_(SQL) 連接 (SQL) - 維基百科，自由的百科全書] | |||
圖片素材 | 圖片素材 | ||