MySQL JOIN 多表連接查詢完整教程：內連接、外連接、語法陷阱與效能優化

多表連接是關係型資料庫的核心能力。本文系統講解 INNER JOIN、LEFT/RIGHT JOIN、CROSS JOIN 語法語義、ON vs WHERE 過濾差異、衍生表/子查詢連接、效能優化原則與常見反模式。

一、連接類型語義對比

二、核心語法與 ON vs WHERE 陷阱

-- 標準語法
SELECT a.id, a.name, b.order_no, b.amount
FROM customers a
LEFT JOIN orders b ON a.id = b.cust_id
WHERE a.status = 1;  -- WHERE 過濾最終結果集

-- ⚠️ 陷阱：LEFT JOIN 右表條件寫在 WHERE 會變成 INNER JOIN
SELECT * FROM customers a
LEFT JOIN orders b ON a.id = b.cust_id
WHERE b.amount > 100;  -- b.amount 為 NULL 的行被過濾，等同 INNER JOIN

-- ✅ 正確：右表過濾條件放在 ON 子句
SELECT * FROM customers a
LEFT JOIN orders b ON a.id = b.cust_id AND b.amount > 100;
-- 此時無訂單或金額≤100 的客戶仍保留，b.* 為 NULL

三、多表連接與衍生表

-- 多表鏈式連接（左深樹，MySQL 單執行緒遞迴執行）
SELECT c.name, o.order_no, oi.product_name, oi.qty
FROM customers c
LEFT JOIN orders o ON c.id = o.cust_id
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE c.status = 1;

-- 衍生表（子查詢）先聚合再連接，減少連接行數
SELECT c.name, o.total_amt
FROM customers c
LEFT JOIN (
    SELECT cust_id, SUM(amount) AS total_amt
    FROM orders
    WHERE created_at >= '2024-01-01'
    GROUP BY cust_id
) o ON c.id = o.cust_id;

四、效能優化鐵律

1. 小表驅動大表：MySQL 優化器傾向用第一張表（驅動表）逐行去匹配被驅動表。確保驅動表行數少、過濾後更少。
2. 被驅動表連接欄位必須有索引： EXPLAIN 中 type=ref、key=idx_xxx，否則全表掃描災難。
3. 只選需要的欄位：避免 SELECT * 產生大量網路傳輸與臨時表。
4. 適當使用 STRAIGHT_JOIN：強制連接順序，僅在優化器選錯時使用。
5. 避免在 ON 中使用函數/運算： ON DATE(a.created) = DATE(b.created) → 無法用索引，改用範圍條件。
6. 大表分頁連接：先用子查詢取主鍵分頁，再回表連接細節，避免 LIMIT 100000, 20 掃描 10 萬行。

五、模擬 FULL OUTER JOIN

SELECT * FROM a LEFT JOIN b ON a.id = b.id
UNION ALL
SELECT * FROM a RIGHT JOIN b ON a.id = b.id WHERE a.id IS NULL;

六、常見反模式與重構

JOIN 是 SQL 表達關係的語言。掌握語義邊界、優化器行為與索引配合，能寫出既正確又高效的多表查詢。