什麼是Inner Join、Outer Join、Union ?

資料處理時經常需要多個表單加起來處理，這個時候你就必須要對於Join跟Union要清楚了解他的作用，我們用實作來了解吧。

假設有以下表單

-- 科系資料表
CREATE TABLE departments(
id INT AUTO_INCREMENT PRIMARY KEY,
dname VARCHAR(20)
);

-- 教師資料表
CREATE TABLE teachers(
id INT AUTO_INCREMENT PRIMARY KEY,
department_id INT NOT NULL,
tname VARCHAR(20),
FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 插入一些科系的範例資料
INSERT INTO departments (dname) VALUES 
('資管系'), ('電機系'), ('資工系');

-- 插入一些教師的範例資料
INSERT INTO teachers (department_id, tname) VALUES
(1, '李一資'), (2, '王二電'), (3, '張三工');

現在要抓出各教師以及所屬科系，如果我們用以下MySQL指令
SELECT dname, tname FROM departments, teachers;

會出現如下的結果

科系中有三筆資料，教師中有三筆資料，如果沒有給任何條件，會出現3乘3筆的資料 (如上圖有9筆資料)。當然，這樣的資料一點意義都沒有。

因此要加上條件，變成以下

SELECT dname, tname FROM departments d, teachers t WHERE t.department_id=d.id;
(列出科系以及所屬老師)

會出現如下的結果

如果想資料清楚一點，就可以使用以科系名稱為群組 (group) :
SELECT dname, tname FROM departments d, teachers t WHERE t.department_id=d.id
GROUP BY dname;

如下圖的結果 (以下因為每個科系都只有一個人，看不出來群組的感覺) : 

以上的語法也可以寫成
SELECT dname, tname  FROM departments d INNER JOIN teachers t ON t.department_id=d.id;

SELECT dname, tname  FROM departments d INNER JOIN teachers t ON t.department_id=d.id
GROUP BY dname;

出現如下的結果，其實跟上圖是一樣的。

INNER JOIN 是什麼? 

是將兩個表單使用外鍵來關聯，並且取其交集的部份。

[INNER JOIN 的寫法有三種方式]
SELECT * FROM tableA a INNER JOIN tableB b ON a.key=b.key;
SELECT * FROM tableA a INNER JOIN tableB b USING (key);
SELECT * FROM tableA a, tableB b WHERE a.key=b.key;

用實際的資料欄位來說，也就是以下的語法

SELECT dname, tname FROM departments d INNER JOIN teachers t ON t.department_id=d.id;
SELECT dname, tname FROM departments d INNER JOIN teachers t USING (id);
SELECT dname, tname FROM departments d, teachers t WHERE t.department_id=d.id;

如果你使用以下語法，會發生錯誤
-- 以下是錯誤的語法
SELECT dname, tname FROM departments d INNER JOIN teachers t USING (department_id);

如下圖，不能使用USING (department_id)，而需要使用USING (id)才行 : 

所以可以知道USING使用的key是第一個選擇表單的主鍵。

再來，假設有以下表單

-- 課程資料表
CREATE TABLE courses (
id INT AUTO_INCREMENT PRIMARY KEY,
cname VARCHAR(50),
department_id INT NOT NULL,
credit INT NOT NULL,
FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 開課資料表
CREATE TABLE open_courses (
id INT AUTO_INCREMENT PRIMARY KEY,
teacher_id INT NOT NULL,
course_id INT NOT NULL,
classroom VARCHAR(20),
FOREIGN KEY (course_id) REFERENCES courses(id),
FOREIGN KEY (teacher_id) REFERENCES teachers(id)
);

-- 開課時間資料表
CREATE TABLE courses_schedule (
id INT AUTO_INCREMENT PRIMARY KEY,
oc_id INT NOT NULL,
slot INT NOT NULL,
FOREIGN KEY (oc_id) REFERENCES open_courses(id)
);

-- 插入一些範例資料

INSERT INTO courses (cname, department_id, credit) VALUES 
('資管概要', 1, 3), ('電磁學', 2, 3), ('程式設計', 3, 2);

INSERT INTO open_courses (teacher_id, course_id, classroom) VALUES 
(1, 1, 'A0001'), (2, 2, 'A0002'), (1, 3, 'A0003');

INSERT INTO courses_schedule (oc_id, slot) VALUES 
(1, 1), (1, 2), (1, 3), (2, 14), (2, 15), (3, 41), (3,42), (3,43);

(slot是什麼? 請參考 https://www.mysql.tw/2023/04/course-registration.html)

什麼是 OUTER JOIN? 

以下使用LEFT OUTER JOIN (也稱為LEFT JOIN)~找出沒有開課的老師

SELECT * FROM teachers t LEFT OUTER JOIN open_courses o ON t.id=o.teacher_id WHERE o.teacher_id IS NULL;

執行結果如下圖

-- 再插入範例資料
INSERT INTO courses (cname, department_id, credit) VALUES ('電路學',3,3);

以下使用RIGHT OUTER JOIN (也稱為RIGHT JOIN)~找出沒有開課的課程

SELECT * FROM open_courses o RIGHT OUTER JOIN courses c ON o.course_id=c.id WHERE o.course_id IS NULL;

執行結果如下圖

JOIN的語法綜合如下 :

[INNER JOIN]
SELECT * FROM tableA a INNER JOIN tableB b ON a.key=b.key;
SELECT * FROM tableA a INNER JOIN tableB b USING (key);
SELECT * FROM tableA a, tableB b WHERE a.key=b.key;

[LEFT OUTER JOIN/LEFT JOIN]
SELECT * FROM tableA a LEFT OUTER JOIN tableB b ON a.key=b.key WHERE [conditions];
SELECT * FROM tableA a LEFT OUTER JOIN tableB b USING (key) WHERE [conditions];

[RIGHT OUTER JOIN/RIGHT JOIN]
SELECT * FROM tableA a RIGHT OUTER JOIN tableB b ON a.key=b.key WHERE [conditions];
SELECT * FROM tableA a RIGHT OUTER JOIN tableB b USING (key) WHERE [conditions];

更多參考資料
https://www.mysql.tw/2018/05/relational-algebra.html

所以可以知道INNER JOIN就是如下圖的概念 : 

內部合併(Inner Join)又稱為自然合併(Natural Join)

更多參考資料
https://www.dofactory.com/sql/inner-join

OUTER JOIN就是如下圖的概念 :

更多參考資料
https://www.dofactory.com/sql/outer-join

什麼是UNION呢?  就是把多個表單合併起來。

-- 再加入學生資料表
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
department_id INT NOT NULL,
sname VARCHAR(20),
FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 再插入一些範例資料

INSERT INTO students (department_id, sname) VALUES 
(1, '吳資管'), (2, '孫電機'), (3, '許資工');

這樣我們就可以把教師跟學生的資料彙整在一起

SELECT tname, department_id FROM teachers
UNION
SELECT sname, department_id FROM students
ORDER BY department_id;

執行結果如下圖