SQL JOIN 多表關聯查詢完全指南
JOIN` 根據關聯欄位將多張表資料水平合併。掌握 `INNER`、`LEFT`、`RIGHT`、`FULL`、`CROSS` 五種連接類型、多表鏈式關聯、效能優化是 SQL 進階必修。
五大 JOIN 類型圖解
| 類型 | 關鍵字 | 結果集 | Venn 圖 |
|---|
| 內連接 | INNER JOIN / JOIN | 兩表**匹配**的行 | 交集 |
| 左外連接 | LEFT [OUTER] JOIN | 左表**所有**行 + 右表匹配行 (無匹配為 NULL) | 左圓完整 |
| 右外連接 | RIGHT [OUTER] JOIN | 右表**所有**行 + 左表匹配行 | 右圓完整 |
| 全外連接 | FULL [OUTER] JOIN | 兩表**所有**行 (無匹配為 NULL) | 並集 |
| 交叉連接 | CROSS JOIN | 笛卡爾積 (所有組合) | 矩形 |
基礎語法
-- 內連接 (最常用)
SELECT t1.col1, t2.col2
FROM table1 t1
INNER JOIN table2 t2 ON t1.common_col = t2.common_col;
-- 左連接 (保留左表所有)
SELECT t1.col1, t2.col2
FROM table1 t1
LEFT JOIN table2 t2 ON t1.common_col = t2.common_col;
-- 多表鏈式關聯
SELECT u.name, o.order_no, oi.product_name, oi.quantity
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
WHERE u.status = 'active';
實戰範例
範例 1：列出所有人的訂單 (含無訂單用戶)
SELECT p.lastname, p.firstname, o.orderno
FROM persons p
LEFT JOIN orders o ON p.id_p = o.id_p;
-- 無訂單用戶：orderno 為 NULL
範例 2：專輯與曲目關聯查詢
SELECT a.title, t.artist
FROM album a
JOIN track t ON a.asin = t.album
WHERE t.song = 'Alison';
範例 3：舊式 WHERE 關聯語法 (不推薦)
SELECT p.lastname, p.firstname, o.orderno
FROM persons p, orders o
WHERE p.id_p = o.id_p;
-- 隱式內連接，易寫成笛卡爾積，現代 SQL 建議顯式 JOIN
進階 JOIN 技巧
多條件關聯
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id AND c.status = 'active'
JOIN addresses a ON c.id = a.customer_id AND a.is_default = 1;
自關聯 (層級結構)
-- 員工-經理關係
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
派生表/子查詢關聯
SELECT u.name, recent_orders.cnt
FROM users u
LEFT JOIN (
SELECT user_id, COUNT(*) AS cnt
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY user_id
) recent_orders ON u.id = recent_orders.user_id;
側向關聯 / LATERAL JOIN (PostgreSQL/MySQL 8.0.14+)
-- 每個用戶取最新 3 筆訂單
SELECT u.name, o.*
FROM users u
CROSS JOIN LATERAL (
SELECT * FROM orders
WHERE user_id = u.id
ORDER BY created_at DESC LIMIT 3
) o;
效能優化核心原則
| 原則 | 說明 | 實踐 |
|---|
| 小表驅動大表 | 驅動表 (外層) 應為結果集較小者 | WHERE 過濾先執行、或用子查詢縮小驅動表 |
| 關聯欄位建索引 | ON 條件欄位必須有索引 | 複合索引順序匹配 JOIN 順序 |
| 避免 SELECT * | 只取需要欄位，減少傳輸、利用覆蓋索引 | 明確列出欄位 |
| WHERE 先於 JOIN 過濾 | 減少參與 JOIN 的行數 | 條件下推、分區裁剪 |
| 優先 INNER JOIN | 優化器選擇空間大 | 外連接限制優化器重排序 |
EXPLAIN 分析關鍵欄位
type：system > const > eq_ref > ref > range > index > ALL (越前越好)key：實際使用的索引rows：預估掃描行數Extra：Using where, Using index (覆蓋索引), Using temporary, Using filesort
常見反模式
- 笛卡爾積：忘寫 ON 條件 或 條件無匹配
- 隱式轉換：
VARCHAR join INT 導致索引失效
- 大表 LEFT JOIN 大表：產生巨大中間結果集
- 子查詢未去相關化：
IN (SELECT ...) 可改寫為 JOIN
MySQL 8.0+ 新特性
LATERAL JOIN / CROSS JOIN LATERALWITH (CTE) 簡化複雜多表查詢INTERSECT / EXCEPT 集合運算替代複雜 JOIN- 隱式索引合併 (Index Merge) 改進