多表查詢是 SQL 核心技能之一。本文系統整理 內連接、外連接、交叉連接、子查詢 等多表查詢語法,對比各類 JOIN 效能差異,並提供索引優化、查詢重寫、避坑指南,助你寫出高效、可維護的多表查詢。
-- 顯式語法(推薦,清晰可讀)
SELECT a.id, a.name, b.order_no, b.amount
FROM users a
INNER JOIN orders b ON a.id = b.user_id
WHERE a.status = 1;
-- 隱式語法(舊標準,不推薦)
SELECT a.id, a.name, b.order_no, b.amount
FROM users a, orders b
WHERE a.id = b.user_id AND a.status = 1;
特點: 只返回兩表匹配成功的行。A 表無對應 B 表資料則不顯示。
SELECT a.id, a.name, b.order_no, b.amount
FROM users a
LEFT JOIN orders b ON a.id = b.user_id AND b.status = 1 -- 條件放 ON 而非 WHERE
WHERE a.status = 1;
關鍵差異:
ON:先關聯再過濾,左表行保留,右表欄位為 NULL。WHERE:關聯後過濾,等同 INNER JOIN。邏輯與 LEFT JOIN 鏡像,實務極少用,**建議統一用 LEFT JOIN**(交換表順序即可),提升可讀性。
-- MySQL 用 UNION 模擬
SELECT * FROM users a LEFT JOIN orders b ON a.id = b.user_id
UNION
SELECT * FROM users a RIGHT JOIN orders b ON a.id = b.user_id;
-- 用戶 → 訂單 → 訂單明細 → 商品
SELECT u.name, o.order_no, od.qty, p.title, p.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_details od ON o.id = od.order_id
INNER JOIN products p ON od.product_id = p.id
WHERE u.status = 1 AND o.status = 2; -- 已付款訂單
建議: 驅動表(結果集最小)放最前,大表靠後;所有 JOIN 欄位必須有索引。
IN/EXISTS 子查詢:SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000)SELECT * FROM (SELECT user_id, SUM(amount) total FROM orders GROUP BY user_id) t JOIN users u ON t.user_id = u.idNOT IN 遇 NULL 值會返回空結果,效能差。WITH)。| 優化點 | 說明 | 驗證方式 |
|---|---|---|
| JOIN 欄位加索引 | ON 條件欄位必須有索引,複合索引遵循最左前綴 | EXPLAIN type=ref/eq_ref |
| 小表驅動大表 | 結果集小的表放前面(驅動表),減少掃描行數 | EXPLAIN rows 欄位 |
| 只選需要欄位 | 避免 SELECT *,減少網路/記憶體/回表開銷 | EXPLAIN Extra: Using index |
| WHERE 條件下推 | MySQL 5.6+ Condition Pushdown,將過濾條件下推到存儲引擎 | EXPLAIN Extra: Using where |
| 避免 Filesort | ORDER BY 欄位建複合索引,順序方向一致 | EXPLAIN Extra: 無 Using filesort |
| 使用覆蓋索引 | 查詢欄位全在索引中,避免回表 | EXPLAIN Extra: Using index |
EXPLAIN 會顯示 type=ALL、rows 極大。JOIN ON a.id = b.user_id 其中一個 INT 一個 VARCHAR → 索引失效。WHERE a.name='x' OR b.title='y' → 無法用索引,拆分為 UNION。WITH user_orders AS (
SELECT user_id, COUNT(*) cnt, SUM(amount) total
FROM orders WHERE status = 2 GROUP BY user_id
),
high_value AS (
SELECT user_id FROM user_orders WHERE total > 10000
)
SELECT u.name, uo.cnt, uo.total
FROM users u
JOIN user_orders uo ON u.id = uo.user_id
JOIN high_value hv ON uo.user_id = hv.user_id;
優勢: 邏輯清晰、可重複引用、優化器可獨立優化各 CTE。
多表查詢核心是 JOIN 類型選擇 + 索引設計 + 執行計畫分析。掌握 INNER/LEFT JOIN 差異、最左前綴原則、覆蓋索引、Condition Pushdown、CTE 重構,並養成 EXPLAIN 驗證習慣,即能應對 90% 以上的多表查詢場景。