連接類型對比表
| 連接類型 | 關鍵字 | 結果集特徵 | 適用場景 |
|---|
| 內連接 | INNER JOIN / JOIN | 僅返回兩表匹配的行 | 核心業務關聯查詢 |
| 左連接 | LEFT JOIN / LEFT OUTER JOIN | 左表全量 + 右表匹配 (無則 NULL) | 主表帶從屬信息查詢 |
| 右連接 | RIGHT JOIN / RIGHT OUTER JOIN | 右表全量 + 左表匹配 (無則 NULL) | 少用，可改 LEFT JOIN 表順序 |
| 全連接 | FULL JOIN / FULL OUTER JOIN | 兩表全量 + 匹配行合併 (MySQL 不直接支援) | 數據對賬、差異分析 |
標準語法範例
-- 內連接：查詢有訂單的用戶
SELECT u.id, u.name, o.order_no, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 左連接：所有用戶及其訂單 (無訂單顯示 NULL)
SELECT u.id, u.name, o.order_no, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 右連接：所有訂單及其用戶 (孤兒訂單用戶顯示 NULL)
SELECT u.id, u.name, o.order_no, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- 全連接模擬 (MySQL 不支援 FULL JOIN)
SELECT u.id, u.name, o.order_no, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.id, u.name, o.order_no, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
多表連接最佳實踐
- 驅動表選擇: 小表驅動大表，左連接左表為驅動表
- 索引覆蓋: ON 條件列、WHERE 過濾列建立組合索引
- 避免 SELECT *: 顯式列出所需列，減少網絡傳輸與臨時表開銷
- 條件下推: WHERE 條件盡量寫在 ON 後 (內連接無差異，外連接有語義差異)
外連接 WHERE 條件陷阱
-- 錯誤：WHERE 過濾右表列會將 LEFT JOIN 退化為 INNER JOIN
SELECT * FROM users u LEFT JOIN orders o ON u.id=o.user_id WHERE o.status='paid';
-- 正確：右表過濾條件放入 ON
SELECT * FROM users u LEFT JOIN orders o ON u.id=o.user_id AND o.status='paid';
性能優化提示
- 使用
EXPLAIN 檢查 type=ref/eq_ref、Extra=Using index
- 大表連接考慮
Hash Join (MySQL 8.0.18+) 或應用層分批查詢
- 關聯子查詢改寫為 JOIN，避免
DEPENDENT SUBQUERY 低效執行計劃
