# **【Part5 - Mysql JOIN 連接的使用】**
:::info
* 什麼是Mysql JOIN?
* FULL JOIN (FULL OUTER JOIN) 全連接
* CROSS JOIN 笛卡爾積連接、交叉連接 (NATURAL JOIN )
* INNER JOIN (JOIN) 尋找交集、內連接
* LEFT JOIN (LEFT OUTER JOIN) 左交集
* RIGHT JOIN (RIGHT OUTER JOIN) 右交集
* 練習
* VIEW 虛擬表格
* [【Mysql 系統權限操作】🔗](https://hackmd.io/@KcUPcvwrTTmVj3rXJd5QKw/BkWZ13NJ6)
* [【Linux 一條龍架站】ft.常見指令🔗](https://hackmd.io/_kNDnzFGTaqkpCm5ztoUYQ?both) LINUX安裝
* [【Part1 - Mysql 是什麼？Mysql 優勢、指令🔗】](https://hackmd.io/@workcata/BJtzg-kyT)
:::
## 什麼是Mysql JOIN?
用於在多個表格之間建立關聯，根據它們之間相關的共同欄位組合起來
網路圖片
<br/>
## 合併表格
### FULL JOIN 全連接
返回兩個表格的所有行，如果沒有匹配的行，會被放置 NULL 值
```=
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
department_id INT
);
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
```
```=
- 插入數據
INSERT INTO employees (employee_id, employee_name, department_id)
VALUES
(1, 'Biga', 1),
(2, 'Cata', 2),
(3, 'Jose', 1),
(4, 'Tomas', NULL);
INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'Marketing');
```
MySQL 中，FULL JOIN（即 FULL OUTER JOIN）是不支援的語法
使用 LEFT JOIN + RIGHT JOIN
```
SELECT e.employee_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.employee_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
```
<br/>
### CROSS JOIN 笛卡爾積連接、交叉連接
除非兩者列數順序、數量相等，不然不能用~ 會變成兩者相乘的大數據集
```=
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
department_id INT
);
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
```
```=
-- 插入數據
INSERT INTO employees (employee_id, employee_name, department_id)
VALUES
(1, 'Biga', 1),
(2, 'Cata', 2),
(3, 'Jose', 1),
(4, 'Tomas', NULL);
INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'Marketing');
```
```=
SELECT e.employee_name, d.department_name
FROM employees e
CROSS JOIN departments d;
```
如果要查詢某些對應條件，用子查詢
```=
SELECT * FROM departments
WHERE department_id = (SELECT department_id FROM employees WHERE employee_name = 'Cata')
```
兩者字段如果都相同，可以用 NATURAL JOIN 自然連接
```=
SELECT *
FROM employees
NATURAL JOIN departments;
# 上方等同下方
SELECT *
FROM employees e
JOIN department d
ON e.department_id = d.department_id;
```
<br/>
### INNER JOIN (JOIN) 尋找交集、內連接
保留兩個 DataFrame 中 'key' 欄位中都存在的鍵值對
```=
CREATE TABLE customers (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
amount DECIMAL(10, 2)
);
```
```=
-- 插入數據
INSERT INTO customers (id, first_name, last_name)
VALUES
(1, 'Biga', 'Lin'),
(2, 'Cata', 'Su'),
(3, 'Tomas', 'Tung'),
(4, 'Eva', 'Zhang');
INSERT INTO orders (order_id, customer_id, order_date, amount)
VALUES
(101, 1, '2023-09-01', 100.50),
(102, 1, '2023-09-05', 75.20),
(103, 2, '2023-09-02', 150.75),
```
```=
SELECT c.first_name, c.last_name, o.order_date, o.amount
FROM customers c
INNER JOIN orders o
ON o.customer_id = c.id;
```
INNER JOIN GROPY BY 尋找交集並分組
```=
SELECT c.first_name, c.last_name, SUM(o.amount) AS total
FROM customers c
JOIN orders o
ON o.customer_id = c.id
GROUP BY c.first_name , c.last_name
ORDER BY total;
```
<br/>
### LEFT JOIN (LEFT OUTER JOIN) 左交集
意思是取左邊表格 table1 數據
如果找不到與右邊表格 table2 匹配的行，將包含 NULL 值
```=
CREATE TABLE customers (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
amount DECIMAL(10, 2)
);
```
```=
-- 插入數據
INSERT INTO customers (id, first_name, last_name)
VALUES
(1, 'Biga', 'Lin'),
(2, 'Cata', 'Su'),
(3, 'Tomas', 'Tung'),
(4, 'Eva', 'Zhang');
INSERT INTO orders (order_id, customer_id, order_date, amount)
VALUES
(101, 1, '2023-09-01', 100.50),
(102, 1, '2023-09-05', 75.20),
(103, 2, '2023-09-02', 150.75),
(104, 3, '2023-09-03', 50.00);
```
```=
SELECT c.first_name, c.last_name, o.order_date, o.amount
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id;
```
如果不想看到右邊表格 table2 有 NULL 值的行
```=
SELECT c.first_name, c.last_name, o.order_date, o.amount
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
WHERE o.customer_id IS NOT NULL;
```
等同於 IN
```=
SELECT c.first_name, c.last_name, o.order_date, o.amount
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
WHERE o.customer_id IN (SELECT id FROM customers WHERE id IS NOT NULL);
```
LEFT JOIN GROPY BY 尋找所有資料，再扣掉交集並分組
```=
SELECT
c.first_name, c.last_name, IFNULL(SUM(o.amount), 0) AS money_spent
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id
GROUP BY
c.first_name,
c.last_name;
```
<br/>
### RIGHT JOIN (RIGHT OUTER JOIN) 右交集
意思是取右邊表格table2 數據
如果找不到與左邊表格table1 匹配的行， 將包含 NULL 值
```=
CREATE TABLE customers (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
amount DECIMAL(10, 2)
);
```
```=
-- 插入數據
INSERT INTO customers (id, first_name, last_name)
VALUES
(1, 'Biga', 'Lin'),
(2, 'Cata', 'Su'),
(3, 'Tomas', 'Tung'),
(4, 'Eva', 'Zhang');
INSERT INTO orders (order_id, customer_id, order_date, amount)
VALUES
(101, 1, '2023-09-01', 100.50),
(102, 1, '2023-09-05', 75.20),
(103, 2, '2023-09-02', 150.75),
(104, 3, '2023-09-03', 50.00);
```
```=
SELECT c.first_name, c.last_name, o.order_date, o.amount
FROM customers c
RIGHT JOIN orders o
ON o.customer_id = c.id;
```
如果不想看到左邊表格 table1 有NULL 值的行
```=
SELECT c.first_name, c.last_name, o.order_date, o.amount
FROM customers c
RIGHT JOIN orders o
ON o.customer_id = c.id
WHERE c.id IS NOT NULL;
```
等同於 IN
```=
SELECT c.first_name, c.last_name, o.order_date, o.amount
FROM customers c
RIGHT JOIN orders o
ON o.customer_id = c.id
WHERE c.id IN (SELECT customer_id FROM orders);
```
ON DELETE CASCADE
<br/>
## 練習 1
```=
CREATE TABLE students (
id INT PRIMARY KEY,
first_name VARCHAR(50)
);
CREATE TABLE papers (
paper_id INT PRIMARY KEY,
student_id INT,
title VARCHAR(100),
grade INT
);
```
```=
INSERT INTO students (id, first_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Cata');
INSERT INTO papers (paper_id, student_id, title, grade) VALUES
(101, 1, 'Math Homework', 95),
(102, 3, 'Science Report', 88);
```
### 如果 `title` 是 NULL，就放 'MISSING'，如果 `grade` 是 NULL，就放 0
```=
SELECT
s.first_name,
IFNULL(p.title, 'MISSING') AS title,
IFNULL(p.grade, 0) AS grade
FROM students s
LEFT JOIN papers p
ON p.student_id = s.id;
```
### 如果 AVG(grade)是 NULL，就放 0，以first_name分群，平均以DESC排序
```=
SELECT
s.first_name,
IFNULL(AVG(p.grade), 0) AS average -- 沒有成績就顯示 0
FROM
students s
LEFT JOIN
papers p
ON
s.id = p.student_id
GROUP BY
s.first_name -- 每個學生一筆資料
ORDER BY
average DESC; -- 平均高的排前面
```
### 加上條件 AVG(grade) > = 75 ，顯示 'passing'，其餘的顯示 'failing'
```=
SELECT
s.first_name,
IFNULL(AVG(p.grade), 0) AS average,
CASE
WHEN IFNULL(AVG(p.grade), 0) >= 75 THEN 'passing'
ELSE 'failing'
END AS status
FROM
students s
LEFT JOIN
papers p ON s.id = p.student_id
GROUP BY
s.first_name
ORDER BY
average DESC;
```
<br/>
## 練習 2
```=
CREATE TABLE series (
id INT PRIMARY KEY,
title VARCHAR(100)
);
CREATE TABLE reviews (
id INT PRIMARY KEY,
rating DECIMAL(3,1),
series_id INT,
reviewer_id INT,
FOREIGN KEY (series_id) REFERENCES series(id)
);
CREATE TABLE reviewers (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
```
```=
INSERT INTO series (id, title) VALUES
(1, 'The Office'),
(2, 'Breaking Bad'),
(3, 'Stranger Things'),
(4, 'Friends'),
(5, 'Malcolm In The Middle'),
(6, 'Pushing Daisies');
INSERT INTO reviews (id, rating, series_id, reviewer_id) VALUES
(1, 8.5, 1, 1),
(2, 9.0, 1, 1),
(3, 7.5, 2, 2),
(4, 8.0, 2, 3),
(5, 8.8, 3, 4),
(6, 9.2, 3, 5),
(7, 6.5, 4, 6);
INSERT INTO reviewers (id, first_name, last_name)
VALUES
(1, 'Thomas', 'Stoneman'),
(2, 'Wyatt', 'Skaggs'),
(3, 'Kimbra', 'Masters'),
(4, 'Domingo', 'Cortez'),
(5, 'Pinkie', 'Petit'),
(6, 'Marlon', 'Croft');
```
### 找到分組平均評分
```=
SELECT
s.title,
ROUND(AVG(r.rating), 2) AS avg_rating
FROM
series s
JOIN
reviews r
ON s.id = r.series_id
GROUP BY
s.title
ORDER BY
avg_rating;
```
### 找到沒有評論的劇
```=
SELECT s.title AS unreviewed_series
FROM series s
LEFT JOIN reviews r
ON s.id = r.series_id
WHERE r.rating IS NULL;
```
### 找到每個人評論紀錄
```=
SELECT
stats.first_name,
stats.last_name,
stats.count,
stats.min,
stats.max,
stats.average,
stats.status
FROM (
SELECT
r.first_name,
r.last_name,
COUNT(rv.rating) AS count,
IFNULL(MIN(rv.rating), 0) AS min,
IFNULL(MAX(rv.rating), 0) AS max,
IFNULL(ROUND(AVG(rv.rating), 2), 0) AS average,
CASE
WHEN COUNT(rv.rating) >= 10 THEN 'POWERUSER'
WHEN COUNT(rv.rating) > 0 THEN 'ACTIVE'
ELSE 'INACTIVE'
END AS status
FROM reviewers r
LEFT JOIN reviews rv ON r.id = rv.reviewer_id
GROUP BY r.first_name, r.last_name
) stats
ORDER BY stats.status DESC, stats.average DESC;
```
用IF 也行
```=
SELECT
r.first_name,
r.last_name,
COUNT(rv.rating) AS count,
IFNULL(MIN(rv.rating), 0) AS min,
IFNULL(MAX(rv.rating), 0) AS max,
ROUND(IFNULL(AVG(rv.rating), 0), 2) AS average,
IF(COUNT(rv.rating) > 0, 'ACTIVE', 'INACTIVE') AS status
FROM reviewers r
LEFT JOIN reviews rv ON r.id = rv.reviewer_id
GROUP BY r.first_name, r.last_name
ORDER BY average DESC;
```
```=
SELECT
r.first_name,
r.last_name,
COUNT(rv.rating) AS count,
IFNULL(MIN(rv.rating), 0) AS min,
IFNULL(MAX(rv.rating), 0) AS max,
ROUND(IFNULL(AVG(rv.rating), 0), 2) AS average,
CASE
WHEN COUNT(rv.rating) >= 10 THEN 'POWERUSER'
WHEN COUNT(rv.rating) > 0 THEN 'ACTIVE'
ELSE 'INACTIVE'
END AS status
FROM reviewers r
LEFT JOIN reviews rv ON r.id = rv.reviewer_id
GROUP BY r.first_name, r.last_name
ORDER BY
CASE
WHEN COUNT(rv.rating) >= 10 THEN 1
WHEN COUNT(rv.rating) > 0 THEN 2
ELSE 3
END,
average DESC;
```
<br/>
## VIEW 虛擬表格
上面範例，直接 CREATE VIEW，可以創建虛擬的表
方便使用者拉資料出來看，原始的表若有修正，view 也會自動修正
| 項目 | 真實資料表（`INSERT INTO` or `CREATE TABLE AS`） | View（`CREATE VIEW`） |
| ------------------- | ----------------------------------------- | ------------------- |
| 資料是否真的存在？ | ✅ 有實際資料儲存在硬碟 | ❌ 沒有資料，只是查詢結果的定義 |
| 每次 SELECT 都重新計算？ | ❌ 不會，除非自己更新 | ✅ 每次 SELECT 都重新查資料 |
| 🛠 可不可以寫入 / 更改？ | ✅ 可以 INSERT/UPDATE/DELETE | ⚠️ 一般不行（除非特別設定） |
| 效能 | ✅ 查詢快，適合大量資料或報表 | ❌ 如果查很大表，效能不佳 |
| 資料會隨原始表更新嗎？ | ❌ 不會，資料靜態 | ✅ 會（因為只是查詢） |
| 用途 | 保存歷史快照、報表資料、ETL 中繼站 | 統一查詢邏輯、簡化程式碼 |
<br/>
取代或創建虛擬表格 (沒有就創建)
```=
CREATE OR REPLACE VIEW full_views AS
SELECT
s.title,
r.rating,
CONCAT(rv.first_name, ' ', rv.last_name) AS reviewer
FROM reviews r
INNER JOIN series s ON r.series_id = s.id
INNER JOIN reviewers rv ON r.reviewer_id = rv.id;
```
```=
SELECT * FROM full_views;
```
也可以下條件
```=
SELECT *
FROM full_views
WHERE rating > 9.0
ORDER BY rating;
```
#### ALTER 修改
MySQL 不支援 ALTER VIEW ... RENAME TO，要用
RENAME
```=
RENAME TABLE full_views TO all_reviews;
```
#### 刪除虛擬表
```=
DROP VIEW <view_name>;
```
Sign in with Wallet
Connect another wallet