JOIN 分類與語義
| 類型 | 關鍵字 | 結果集 | 韋恩圖 |
| 內連接 | INNER JOIN / JOIN | 兩表**匹配行的交集** | A ∩ B |
| 左連接 | LEFT [OUTER] JOIN | **左表全量** + 右表匹配行(無匹配填 NULL) | A 全部 |
| 右連接 | RIGHT [OUTER] JOIN | **右表全量** + 左表匹配行 | B 全部 |
| 全連接 | FULL [OUTER] JOIN | 兩表全量(MySQL 不直接支持,用 UNION 模擬) | A ∪ B |
核心語法模板
SELECT select_list
FROM table_a a
[INNER | LEFT | RIGHT] JOIN table_b b
ON a.join_col = b.join_col
[WHERE ...]
[GROUP BY ...] ...
關鍵點: ON 條件決定**如何匹配**;WHERE 條件決定**最終保留哪些行**。LEFT JOIN 中右表條件放在 WHERE 會隱式轉為內連接。
實戰案例:教程表 + 統計表
基礎數據
-- tutorials_tbl
+----+---------------+-----------------+
| id | tutorial_title| tutorial_author |
+----+---------------+-----------------+
| 1 | Learn PHP | John Poul |
| 2 | Learn MySQL | Abdul S |
| 3 | JAVA Tutorial | Sanjay |
+----+---------------+-----------------+
-- tcount_tbl
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran | 20 |
| John Poul | 1 |
| Sanjay | 1 |
+-----------------+----------------+
1. 內連接:僅顯示有統計的作者
SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
FROM tutorials_tbl a
INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author;
-- 結果:John Poul(1), Sanjay(1) —— Abdul S 缺失
2. 左連接:保留所有教程作者
SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
FROM tutorials_tbl a
LEFT JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author;
-- 結果:
-- 1 | John Poul | 1
-- 2 | Abdul S | NULL
-- 3 | Sanjay | 1
3. 右連接:保留所有統計記錄
SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
FROM tutorials_tbl a
RIGHT JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author;
-- 結果:包含 mahran (左表無匹配 → tutorial_id=NULL)
多表關聯:三表 JOIN
SELECT g.goods_name, c.cat_name, b.brand_name
FROM ecs_goods g
LEFT JOIN ecs_category c ON g.cat_id = c.cat_id
LEFT JOIN ecs_brand b ON g.brand_id = b.brand_id
WHERE g.cat_id = 4;
**執行順序**:先 g LEFT JOIN c 生成中間表,再 LEFT JOIN b。左表始終保留 ecs_goods 全量。
經典面試題:足球賽程雙向關聯隊伍表
表結構
CREATE TABLE `match` (
`mid` INT PRIMARY KEY,
`hid` INT, -- 主隊 ID
`gid` INT, -- 客隊 ID
`mres` VARCHAR(10), -- 比分
`matime` DATE
);
CREATE TABLE `team` (
`tid` INT PRIMARY KEY,
`tname` VARCHAR(20)
);
需求:列出 2006-06-01 到 2006-07-01 的比賽,格式:主隊 比分 客隊 時間
SELECT
t1.tname AS home_team,
m.mres AS score,
t2.tname AS guest_team,
m.matime
FROM `match` m
JOIN team t1 ON m.hid = t1.tid -- 主隊
JOIN team t2 ON m.gid = t2.tid -- 客隊
WHERE m.matime BETWEEN '2006-06-01' AND '2006-07-01';
關鍵技巧: 同一張表關聯多次,**必須使用不同別名** (t1、t2) 區分角色。
JOIN 執行原理與性能優化
Nested Loop Join (NLJ) — MySQL 唯一算法
- 選擇**驅動表**(通常是小表/過濾後行數少的表)。
- 逐行讀取驅動表,到**被驅動表**查找匹配行(利用索引)。
- 匹配成功輸出,失敗則看 JOIN 類型:INNER 丟棄,LEFT 輸出 NULL。
優化鐵律
| 原則 | 執行動作 |
| 被驅動表關聯列**必須有索引** | EXPLAIN 查看 type=ref、key=idx_xxx |
| 小表驅動大表 | WHERE 過濾後行數少的表做驅動表 |
| 只查需要的列 | 避免 SELECT *,減少網絡/內存/磁盤 IO |
| 條件下推 | WHERE 條件盡量寫在 ON 子句(內連接等價)或驅動表 WHERE |
| 避免子查詢 | 用 JOIN 替代 IN/EXISTS 子查詢(8.0+ 優化器可自動轉換) |
EXPLAIN 關鍵列解讀
EXPLAIN SELECT ... FROM a LEFT JOIN b ON a.id = b.a_id;
+----+-------------+-------+------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 1000 | |
| 1 | SIMPLE | b | ref | idx_a_id | idx_a_id| 5 | a.id | 1 | Using where |
+----+-------------+-------+------+---------------+---------+---------+------+------+-------------+
type=ALL:全表掃描(驅動表無索引可用)
type=ref:非唯一索引查找(被驅動表用上索引)
type=eq_ref:唯一索引/主鍵查找(最理想)
FULL JOIN 模擬(MySQL 不原生支持)
SELECT * FROM a LEFT JOIN b ON a.id = b.id
UNION
SELECT * FROM a RIGHT JOIN b ON a.id = b.id;
避坑清單
- LEFT JOIN 右表條件寫在 WHERE → 變內連接。正確:
LEFT JOIN b ON a.id=b.id AND b.status=1。
- 字符集/校對規則不一致 導致索引失效:
utf8mb4_general_ci vs utf8mb4_bin。
- 隱式類型轉換:
VARCHAR 列 = 數字 導致列索引失效,用 CAST 或統一類型。
- 過早優化:小表(<1000 行)全表掃描比索引查找更快,優化器會自動選擇。
總結
- INNER JOIN:交集,雙向過濾。
- LEFT JOIN:左表全量,右表補充,業務主表在左。
- RIGHT JOIN:右表全量,可改寫為 LEFT JOIN(交換表順序),統一團隊編碼風格。
- **索引是 JOIN 性命線**——被驅動表關聯列無索引 = 災難性性能。
- MySQL 8.0+ 支持
LATERAL JOIN、CTE、窗口函數,複雜關聯查詢更優雅。