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

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 筆（id 1~5），id 6~10 無部門資料被過濾。

2. 左外連接（LEFT JOIN）

返回左表所有行，右表無匹配則為 NULL。

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

結果：10 筆，id 6~10 的 dept_id 為 NULL。

3. 右外連接（RIGHT JOIN）

返回右表所有行，左表無匹配則為 NULL。
LEFT JOIN 與 RIGHT JOIN 互換表順序即可，習慣統一用 LEFT JOIN 閱讀性更好。

4. 交叉連接（CROSS JOIN）

笛卡兒積：左表每行與右表每行組合。極度慎用，資料量大時會爆炸。

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

結果：10 × 5 = 50 筆。

5. 多表連接鏈

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;

效能關鍵點

• ON 條件欄位必須有索引：emp.id（PK）、emp_dept.emp_id（PK 一部分）已滿足。
• 驅動表選擇：MySQL 優化器傾向用「小結果集」驅動「大表」。若需控制，可用 STRAIGHT_JOIN 或 USE INDEX。
• 避免 SELECT *：只取需要欄位，減少網路與記憶體開銷。
• WHERE 條件下推：外連接中，右表條件放在 ON 而非 WHERE，否則退化為內連接。

常見陷阱

1. LEFT JOIN 右表條件寫在 WHERE：導致 NULL 行被過濾，變成內連接。正確寫法移至 ON。
2. 隱式類型轉換：VARCHAR join INT 會導致全表掃描，務必保持類型一致。
3. 過度連接：單查詢超過 5-6 張表建議拆分或用臨時表/物化視圖。