•【Part 5】MySQL JOIN 教學：INNER、LEFT、RIGHT、FULL、CROSS JOIN 完整範例與VIEW虛擬表解析

什麼是Mysql JOIN?

用於在多個表格之間建立關聯，根據它們之間相關的共同欄位組合起來

網路圖片

合併表格

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;

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;

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;

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;

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

練習 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;

練習 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;

VIEW 虛擬表格

上面範例，直接 CREATE VIEW，可以創建虛擬的表

方便使用者拉資料出來看，原始的表若有修正，view 也會自動修正

項目	真實資料表（INSERT INTO or CREATE TABLE AS）	View（CREATE VIEW）
資料是否真的存在？	✅ 有實際資料儲存在硬碟	❌ 沒有資料，只是查詢結果的定義
每次 SELECT 都重新計算？	❌ 不會，除非自己更新	✅ 每次 SELECT 都重新查資料
🛠 可不可以寫入 / 更改？	✅ 可以 INSERT/UPDATE/DELETE	⚠️ 一般不行（除非特別設定）
效能	✅ 查詢快，適合大量資料或報表	❌ 如果查很大表，效能不佳
資料會隨原始表更新嗎？	❌ 不會，資料靜態	✅ 會（因為只是查詢）
用途	保存歷史快照、報表資料、ETL 中繼站	統一查詢邏輯、簡化程式碼

取代或創建虛擬表格 (沒有就創建)

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>;