MySQL 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 類型與結果對比

1. INNER JOIN (內連接) — 只有匹配行

SELECT e.id, e.name, d.dept_id
FROM emp e
INNER JOIN emp_dept d ON e.id = d.emp_id;

結果 (5 行)：只有 emp_id 在 1-5 的員工出現，6-10 被過濾。

2. LEFT JOIN (左外連接) — 左表全保留

SELECT e.id, e.name, d.dept_id
FROM emp e
LEFT JOIN emp_dept d ON e.id = d.emp_id;

結果 (10 行)：emp 全部保留，無匹配部門的 (6-10) dept_id=NULL。

3. RIGHT JOIN (右外連接) — 右表全保留

SELECT e.id, e.name, d.dept_id
FROM emp e
RIGHT JOIN emp_dept d ON e.id = d.emp_id;

結果 (5 行)：emp_dept 全部保留 (無孤兒行)，等同 INNER JOIN (因 FK 關係)。

4. CROSS JOIN (交叉連接) — 笛卡爾積

SELECT e.name, d.dept_id
FROM emp e
CROSS JOIN emp_dept d;

結果 (50 行)：10 × 5，**極度危險，嚴禁無條件使用**。

進階場景

多表連接 (3 表以上)

SELECT e.name, d.dept_id, p.project_name
FROM emp e
LEFT JOIN emp_dept d ON e.id = d.emp_id
LEFT JOIN emp_project p ON e.id = p.emp_id
WHERE d.dept_id = 'R&D';

**原則**：小表驅動大表、先過濾後連接、關鍵列建索引。

條件下推與 WHERE 陷阱

-- 正確：ON 條件控制連接匹配，左表保留
SELECT e.name, d.dept_id
FROM emp e
LEFT JOIN emp_dept d ON e.id = d.emp_id AND d.dept_id = 'R&D';

-- 錯誤：WHERE 過濾將 LEFT JOIN 退化為 INNER JOIN
SELECT e.name, d.dept_id
FROM emp e
LEFT JOIN emp_dept d ON e.id = d.emp_id
WHERE d.dept_id = 'R&D';  -- NULL 不等於 'R&D'，導致無匹配行被過濾

USING 簡寫 (同名列)

SELECT * FROM emp e
INNER JOIN emp_dept d USING (emp_id);  -- 需 emp 表主鍵也叫 emp_id

性能優化鐵律

1. 關鍵列建索引：emp.id (PK)、emp_dept.emp_id (PK 部分/二級索引)
2. 小結果集驅動大表：用子查詢/臨時表先過濾
3. 避免 SELECT *：只取需要列，利用覆蓋索引
4. EXPLAIN 觀察 type/ref/rows：目標 ref/eq_ref，rows 接近預期
5. 衍生表物化優化 (5.6+)：derived_merge 控制子查詢合併

常見反模式

反模式	後果	修正
大表做驅動表	掃描海量行、臨時表落盤	子查詢先過濾、或調整 JOIN 順序 (STRAIGHT_JOIN)
ON 條件用函數/運算	索引失效、全表掃描	移至應用層預計算、或函數索引 (8.0+)
隱式類型轉換	VARCHAR = INT 導致索引失效	統一類型、顯式 CAST
OR 條件跨表	優化器無法使用索引合併	拆分為 UNION ALL

結語

JOIN 是關係型數據庫的靈魂操作。**內連接過濾、左連接保留、右連接少用、交叉連接禁用**。掌握 ON vs WHERE 語義差異、索引覆蓋設計、EXPLAIN 執行計劃解讀，是寫出高性能 SQL 的必修課。記住：**最好的優化是不需要 JOIN (合理冗余/廣播表/應用層組裝)**，其次是 **讓優化器有好索引可用**。