Visual explanation of MySQL joins
Jump to navigation
Jump to search
使用文氏圖 (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 欄位都是主鍵( PRIMARY KEY )。
CREATE TABLE table_a (id int NOT NULL PRIMARY KEY, name varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL); INSERT INTO table_a (id, name) VALUES (1, '小一'), (2, '小二'), (3, '小三'), (4, '小四') ; CREATE TABLE table_b (id int NOT NULL PRIMARY KEY, gender varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL); INSERT INTO table_b (id, gender) VALUES (1, '女'), (3, '女'), (5, '男'), (6, '不明') ;
預覽測試資料
兩個資料表都有的資料,以紅色標示:
table_a |
table_b |
狀況 1-1: 以 a 資料為主,再把 b 資料黏上
如果 a 資料比 b 資料的資料筆數少,將會以 a 資料筆數為主,部分 b 資料會遺失。
MySQL 資料庫查詢:
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;
預覽輸出資料
1|小一|1|女 3|小三|3|女 2|小二|NULL|NULL 4|小四|NULL|NULL
狀況 1-2: 以 b 資料為主,再把 a 資料黏上
MySQL 資料庫查詢:
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;
預覽輸出資料
1|小一|1|女 3|小三|3|女 NULL|NULL|5|男 NULL|NULL|6|不明
狀況 2: 交集的資料,同時存在於 a 和 b
MySQL 資料庫查詢:
SELECT * FROM table_a INNER JOIN table_b ON table_a.id = table_b.id; /*相同效果的別種寫法*/ SELECT * FROM table_a, table_b WHERE table_a.id = table_b.id; 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;
預覽輸出資料
1|小一|1|女 3|小三|3|女
狀況 3-1: 相對差集的資料,存在於 a,但是不存在於 b
存在於資料表 table_a ,但是不存在於 table_b
MySQL 資料庫查詢:
SELECT * FROM table_a LEFT JOIN table_b ON table_a.id = table_b.id WHERE table_b.id IS NULL;
預覽輸出資料
2|小二|NULL|NULL 4|小四|NULL|NULL
狀況 3-2: 相對差集的資料,存在於 b,但是不存在於 a
存在於資料表 table_b ,但是不存在於 table_a
MySQL 資料庫查詢:
SELECT * FROM table_a RIGHT JOIN table_b ON table_a.id = table_b.id WHERE table_a.id IS NULL;
預覽輸出資料
NULL|NULL|5|男 NULL|NULL|6|不明
狀況 4: 聯集的資料,a 和 b 的所有資料
MySQL 資料庫查詢 (相當於 SQL 的 FULL OUTER JOIN):
SELECT * FROM table_a LEFT JOIN table_b ON table_a.id = table_b.id UNION SELECT * FROM table_a RIGHT JOIN table_b ON table_a.id = table_b.id;
預覽輸出資料
1|小一|1|女 3|小三|3|女 2|小二|NULL|NULL 4|小四|NULL|NULL NULL|NULL|5|男 NULL|NULL|6|不明
狀況 5: 對稱差集的資料,只存在於 a 或 b 其中一個
對稱差集 (Symmetric difference) 或稱 邏輯異或 (exclusive or) 的資料,只存在於 table_a 或 table_b 其中一個
MySQL 資料庫查詢:
SELECT * FROM table_a LEFT JOIN table_b ON table_a.id = table_b.id WHERE table_a.id IS NULL OR table_b.id IS NULL UNION SELECT * FROM table_a RIGHT JOIN table_b ON table_a.id = table_b.id WHERE table_a.id IS NULL OR table_b.id IS NULL;
預覽輸出資料
2|小二|NULL|NULL 4|小四|NULL|NULL NULL|NULL|5|男 NULL|NULL|6|不明
參考資料
- A Visual Explanation of SQL Joins
- sql - Difference between INNER and OUTER joins - Stack Overflow
- (MySQL)left, right, inner, outer join 使用方法 - 小惡魔 - 電腦技術 - 工作筆記 - AppleBOY
- 維基百科:數學專題/集合論 - 維基百科,自由的百科全書
- Steve Stedman (2015). MySQL JOIN Types Poster - Steve Stedman [Last visited: 2017-02-21]
- 連接 (SQL) - 維基百科,自由的百科全書
圖片素材