Visual explanation of MySQL joins

From LemonWiki共筆
Jump to: navigation, search

使用文氏圖 (Venn diagram) 視覺化解釋 MySQL Joins (RIGHT Join、LEFT Join、INNER Join、OUTER Join) 的差異

摘要[edit]

  1. 狀況 1-1: 以 a 資料為主,再把 b 資料黏上: LEFT JOIN
    • 如果 a 資料有,但是 b 資料沒有。 b 欄位顯示 null
  2. 狀況 2: 交集 (set intersection) 的資料,同時存在於 a 和 b: INNER JOIN
    • 如果 a 資料有,但是 b 資料沒有。 不會顯示
  3. 狀況 3-1: 相對差集 (set difference) 的資料,存在於 a,但是不存在於 b: LEFT JOIN 並且加上 b 資料 id 欄位值是 null 做查詢
  4. 狀況 4: 聯集 (set union) 的資料,a 和 b 的所有資料
    • 如果 a 資料有,但是 b 資料沒有。 b 欄位顯示 null
  5. 狀況 5: 對稱差集 (set symmetric difference) 的資料,只存在於 a 或 b 其中一個

準備測試資料[edit]

兩個資料表的 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, '不明')
;

預覽測試資料[edit]

兩個資料表都有的資料,以紅色標示:

table_a
id name
-- -------
1 小一
2 小二
3 小三
4 小四

table_b
id gender
-- --------
1 女
3 女
5 男
6 不明

狀況 1-1: 以 a 資料為主,再把 b 資料黏上[edit]

Icon exclaim.gif 如果 a 資料比 b 資料的資料筆數少,將會以 a 資料筆數為主,部分 b 資料會遺失。

Venn_diagrams_proposition_a.png

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;

預覽輸出資料[edit]

1|小一|1|女
3|小三|3|女
2|小二|NULL|NULL
4|小四|NULL|NULL

狀況 1-2: 以 b 資料為主,再把 a 資料黏上[edit]

Venn_diagrams_proposition_b.png

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;

預覽輸出資料[edit]

1|小一|1|女
3|小三|3|女
NULL|NULL|5|男
NULL|NULL|6|不明

狀況 2: 交集的資料,同時存在於 a 和 b[edit]

Venn_diagrams_intersection_of_two_sets.png

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;

預覽輸出資料[edit]

1|小一|1|女
3|小三|3|女

狀況 3-1: 相對差集的資料,存在於 a,但是不存在於 b[edit]

存在於資料表 table_a ,但是不存在於 table_b

Venn_diagrams_relative_complement_of_b_(right)_in_a_(left).png

MySQL 資料庫查詢:

SELECT * FROM table_a
LEFT JOIN table_b
ON table_a.id = table_b.id
WHERE table_b.id IS NULL;

預覽輸出資料[edit]

2|小二|NULL|NULL
4|小四|NULL|NULL

狀況 3-2: 相對差集的資料,存在於 b,但是不存在於 a[edit]

存在於資料表 table_b ,但是不存在於 table_a

Venn_diagrams_relative_complement_of_a_(left)_in_b_(right).png

MySQL 資料庫查詢:

SELECT * FROM table_a
RIGHT JOIN table_b
ON table_a.id = table_b.id
WHERE table_a.id IS NULL;

預覽輸出資料[edit]

NULL|NULL|5|男
NULL|NULL|6|不明

狀況 4: 聯集的資料,a 和 b 的所有資料[edit]

Venn_diagrams_union_of_two_sets.png

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;

預覽輸出資料[edit]

1|小一|1|女
3|小三|3|女
2|小二|NULL|NULL
4|小四|NULL|NULL
NULL|NULL|5|男
NULL|NULL|6|不明

狀況 5: 對稱差集的資料,只存在於 a 或 b 其中一個[edit]

對稱差集 (Symmetric difference) 或稱 邏輯異或 (exclusive or) 的資料,只存在於 table_a 或 table_b 其中一個

Venn_diagrams_symmetric_difference_of_two.png

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;

預覽輸出資料[edit]

2|小二|NULL|NULL
4|小四|NULL|NULL
NULL|NULL|5|男
NULL|NULL|6|不明

參考資料[edit]

圖片素材