本日主題：把多張表的資料關聯起來查詢 + VIEW 觀念 預計時間：2 小時 對應主教材：第 6.1 節

一、今日學習目標

  • [ ] 理解為什麼要 JOIN
  • [ ] 會寫 INNER JOIN 與 LEFT JOIN
  • [ ] 理解各種 JOIN 類型的差別
  • [ ] 認識 VIEW（檢視表）的觀念
  • [ ] 會寫 JOIN + 子查詢的組合

二、教材內容

2.1 為什麼要 JOIN？

資料庫為了避免重複，會把資料拆成多張表（正規化）。例如 employees 只存 dept_id，部門名稱放在 departments。要同時看「員工名 + 部門名」，就要把兩張表關聯（JOIN）起來。

employees                 departments
id  name    dept_id       id  dept_name
1   王小明   1     ───┐    1   IT
2   李大華   1     ───┤    2   會計
3   陳美麗   2     ───┘──▶ 3   維修
4   林志強   3     ───────▶
5   張雅婷   1
6   黃建宏   3

透過 dept_id 和 departments.id 建立關聯

2.2 INNER JOIN（最常用）

只回傳「兩邊都對得上」的資料：

SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;
  • e、d 是表的別名（alias），讓 SQL 更簡潔。
  • ON 指定關聯條件（哪兩個欄位對應）。
  • JOIN 等同於 INNER JOIN。
INNER JOIN 圖解：
┌─────────┐   ┌───────────┐
│employees │   │departments │
│          │   │            │
│    ┌─────┼───┼─────┐      │
│    │ 交集 │   │     │      │
│    │     │   │     │      │
│    └─────┼───┼─────┘      │
│          │   │            │
└─────────┘   └───────────┘
只回傳交集（兩邊都對得上的資料）

2.3 LEFT JOIN

保留「左表」全部資料，右表沒對到的補 NULL：

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
  • 用途：想看「所有員工，即使部門資料缺失也要列出」。
LEFT JOIN 圖解：
┌─────────┐   ┌───────────┐
│employees │   │departments │
│ ████████ │   │            │
│ ████┌────┼───┼─────┐      │
│ ████│交集 │   │     │      │
│ ████│    │   │     │      │
│ ████└────┼───┼─────┘      │
│ ████████ │   │            │
└─────────┘   └───────────┘
保留左表全部 + 交集部分
右表沒對到的欄位補 NULL

2.4 JOIN 類型對照

類型 回傳 使用場景
INNER JOIN 兩邊都有對到的 最常用，只要匹配的資料
LEFT JOIN 左表全部 + 右表對到的 要看左表全部，即使沒有匹配
RIGHT JOIN 右表全部 + 左表對到的 較少用，可用 LEFT JOIN 互換
FULL JOIN 兩邊全部 要看所有資料（部分資料庫不支援）
CROSS JOIN 笛卡爾積（所有組合） 很少用，產生大量資料

2.5 JOIN 常見錯誤

-- ❌ 錯誤 1：忘記 ON 條件
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d;        -- 沒有 ON → 變成 CROSS JOIN（笛卡爾積）

-- ❌ 錯誤 2：ON 條件寫錯欄位
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.id = d.id;  -- 應該用 e.dept_id = d.id

-- ❌ 錯誤 3：欄位名稱衝突沒加表別名
SELECT id, name, dept_name           -- id 在兩張表都有，會報錯
FROM employees e
JOIN departments d ON e.dept_id = d.id;
-- 正確：SELECT e.id, e.name, d.dept_name

2.6 JOIN + 分組（綜合）

-- 每個部門的人數（用部門名顯示）
SELECT d.dept_name, COUNT(e.id) AS 人數
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.dept_name;

-- 每個部門的平均薪水（只顯示有人的部門）
SELECT d.dept_name, AVG(e.salary) AS 平均薪水
FROM departments d
JOIN employees e ON e.dept_id = d.id
GROUP BY d.dept_name;

2.7 JOIN + 子查詢

-- 找出薪水高於其部門平均的員工
SELECT e.name, e.salary, e.department, dept_avg.avg_sal
FROM employees e
JOIN (
  SELECT department, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
) AS dept_avg ON e.department = dept_avg.department
WHERE e.salary > dept_avg.avg_sal;

2.8 VIEW（檢視表）觀念

VIEW 是一個「儲存的查詢」，像是虛擬的表格，不實際儲存資料：

-- 建立 VIEW
CREATE VIEW employee_detail AS
SELECT e.name, e.salary, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

-- 使用 VIEW（跟查詢一般表一樣）
SELECT * FROM employee_detail;
SELECT * FROM employee_detail WHERE dept_name = 'IT';

-- 刪除 VIEW
DROP VIEW employee_detail;

VIEW 的優點：

┌──────────────────────────────────────────────┐
│              VIEW 的好處                      │
├──────────────────────────────────────────────┤
│                                                │
│  ✓ 簡化複雜查詢：把常用的 JOIN 查詢存成 VIEW    │
│  ✓ 安全性：只讓使用者看到特定欄位               │
│    （如 VIEW 不含薪水欄位）                     │
│  ✓ 一致性：所有人用同一個 VIEW，避免寫錯        │
│  ✓ 維護性：底層表改了，只需修改 VIEW 定義       │
│                                                │
│  注意：VIEW 不儲存資料，每次查詢都會執行底層 SQL │
└──────────────────────────────────────────────┘

常見錯誤示範：

-- ❌ 錯誤：對 VIEW 做 INSERT（多數 VIEW 不支援寫入）
INSERT INTO employee_detail VALUES ('新員工', 50000, 'IT');
-- VIEW 是虛擬表，通常只能讀取

-- ❌ 錯誤：以為 VIEW 會自動更新
-- VIEW 不儲存資料，底層表的資料變了，VIEW 的結果會自動反映
-- 但 VIEW 的「定義」不會自動改變

三、常見誤解

誤解 正確觀念
「JOIN 會把兩張表合併成一張」 JOIN 是查詢時關聯，不會改變原表
「LEFT JOIN 和 RIGHT JOIN 效果一樣」 不同，取決於哪張表在左邊。LEFT JOIN 保留左表全部
「VIEW 會佔用儲存空間」 VIEW 不儲存資料，只儲存查詢定義。Materialized View（物化檢視）才會
「JOIN 越多越好」 JOIN 太多會影響效能，應只 JOIN 需要的表

四、面試加分小知識

  • Self JOIN：同一張表自己 JOIN 自己，用於「主管-部屬」等層級關係。
  • Materialized View（物化檢視）：與 VIEW 不同，會實際儲存資料，需定期更新。
  • N+1 Problem：ORM 中常見的效能問題，沒用好 JOIN 導致多次查詢。
  • Cartesian Product（笛卡爾積）：CROSS JOIN 或忘記 ON 條件時產生，列數 = 左表 × 右表。

五、今日練習

Q1. 查出每位員工的姓名與其部門名稱（用 dept_id 關聯）。

Q2. INNER JOIN 和 LEFT JOIN 的差別？

Q3. 用 JOIN + GROUP BY 算出每個部門的平均薪水（顯示部門名稱）。

Q4. 用 LEFT JOIN 找出「沒有任何員工的部門」。

Q5. 建立一個 VIEW，顯示員工姓名、薪水、部門名稱。然後用這個 VIEW 查詢 IT 部門的資料。

參考解答 
-- A1：員工姓名 + 部門名稱
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

-- A2：
-- INNER JOIN 只回傳兩邊都對得上的列
-- LEFT JOIN 保留左表全部，右表沒對到的補 NULL
-- 例如：有員工 dept_id=99 但 departments 沒有 id=99
--   INNER JOIN 不會顯示這個員工
--   LEFT JOIN 會顯示這個員工，dept_name 為 NULL

-- A3：每部門平均薪水（用部門名稱）
SELECT d.dept_name, AVG(e.salary) AS 平均薪水
FROM departments d
JOIN employees e ON e.dept_id = d.id
GROUP BY d.dept_name;

-- A4：沒有員工的部門
SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.id IS NULL;
-- 說明：LEFT JOIN 後，沒有員工的部門，e.id 會是 NULL

-- A5：建立 VIEW 並查詢
CREATE VIEW employee_detail AS
SELECT e.name AS 姓名, e.salary AS 薪水, d.dept_name AS 部門
FROM employees e
JOIN departments d ON e.dept_id = d.id;

SELECT * FROM employee_detail WHERE 部門 = 'IT';

六、延伸閱讀

七、今日檢核

  • [ ] 我理解為什麼需要 JOIN
  • [ ] 我會寫 INNER JOIN 與 LEFT JOIN
  • [ ] 我能說出各種 JOIN 類型的差別
  • [ ] 我會用 JOIN + GROUP BY 做統計
  • [ ] 我理解 VIEW 的觀念與用法

⬅️ 上一天：Day 3 ｜ 🏠 本週總覽 ｜ ➡️ 下一天：Day 5 — 新增/更新/刪除