MySQL JOIN 完整詳解：INNER、LEFT、RIGHT、CROSS、SELF JOIN 範例與效能優化

建立範例資料

CREATE TABLE emp (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(10)
);

CREATE TABLE emp_dept (
    dept_id VARCHAR(4) NOT NULL,
    emp_id INT NOT NULL,
    emp_name VARCHAR(10),
    PRIMARY KEY (dept_id, emp_id)
);

INSERT INTO emp VALUES 
(1,'Dennis-1'),(2,'Dennis-2'),(3,'Dennis-3'),(4,'Dennis-4'),(5,'Dennis-5'),
(6,'Dennis-6'),(7,'Dennis-7'),(8,'Dennis-8'),(9,'Dennis-9'),(10,'Dennis-10');

INSERT INTO emp_dept VALUES
('R&D',1,'Dennis-1'),('Dev',2,'Dennis-2'),('R&D',3,'Dennis-3'),
('Test',4,'Dennis-4'),('Test',5,'Dennis-5');

五大 JOIN 類型圖解

MySQL 具體語法與優化

-- 1. 明確指定 JOIN 類型 (INNER 可省略)
SELECT e.name, d.dept_id 
FROM emp e 
JOIN emp_dept d ON e.id = d.emp_id;  -- 等同 INNER JOIN

-- 2. 多表 JOIN 結合順序影響效能
-- 小表驅動大表：先篩選結果集較小的表
SELECT * FROM small_table s JOIN large_table l ON s.id = l.s_id;

-- 3. USING 簡化同名欄位 JOIN
SELECT * FROM emp e JOIN emp_dept d USING (emp_id);  -- 需兩表欄位同名

-- 4. STRAIGHT_JOIN 強制順序 (慎用)
SELECT * FROM emp e STRAIGHT_JOIN emp_dept d ON e.id = d.emp_id;

效能優化關鍵點

1. JOIN 條件欄位必須有索引： emp.id (PK 已有)、emp_dept.emp_id (PK 一部分) 已滿足。
2. 驅動表選擇： MySQL 優化器傾向選擇結果集較小的表為驅動表。可用 EXPLAIN 驗證 type 欄位是否為 ref/eq_ref。
3. 避免 SELECT *： 只查詢需要欄位，減少網路傳輸、利用覆蓋索引。
4. WHERE 條件下推： 將過濾條件盡早套用 (驅動表 WHERE)，縮小 JOIN 運算集合。
5. 衍生表/子查詢優化： MySQL 5.6+ 支援衍生表合併，舊版本需手動改寫為 JOIN。

常見陷阱

• LEFT JOIN + WHERE 右表條件 = 變相 INNER JOIN：

  -- 錯誤：WHERE d.dept_id = 'R&D' 導致無匹配行被過濾
  SELECT * FROM emp e LEFT JOIN emp_dept d ON e.id = d.emp_id WHERE d.dept_id = 'R&D';
  
  -- 正確：條件移至 ON
  SELECT * FROM emp e LEFT JOIN emp_dept d ON e.id = d.emp_id AND d.dept_id = 'R&D';
  

• NULL 值比較： ON e.id = d.emp_id 中若任一邊為 NULL，比較結果為 UNKNOWN，不會匹配 (符合 SQL 標準)。
• 字元集/排序規則不一致： JOIN 欄位 charset/collation 不同會導致隱含轉換、索引失效。