萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL JOIN 多表關聯完全指南:INNER/LEFT/RIGHT JOIN 語法、原理、性能優化與實戰案例

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 唯一算法

  1. 選擇**驅動表**(通常是小表/過濾後行數少的表)。
  2. 逐行讀取驅動表,到**被驅動表**查找匹配行(利用索引)。
  3. 匹配成功輸出,失敗則看 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;

避坑清單

  1. LEFT JOIN 右表條件寫在 WHERE → 變內連接。正確:LEFT JOIN b ON a.id=b.id AND b.status=1。
  2. 字符集/校對規則不一致 導致索引失效:utf8mb4_general_ci vs utf8mb4_bin。
  3. 隱式類型轉換:VARCHAR 列 = 數字 導致列索引失效,用 CAST 或統一類型。
  4. 過早優化:小表(<1000 行)全表掃描比索引查找更快,優化器會自動選擇。

總結

  • INNER JOIN:交集,雙向過濾。
  • LEFT JOIN:左表全量,右表補充,業務主表在左。
  • RIGHT JOIN:右表全量,可改寫為 LEFT JOIN(交換表順序),統一團隊編碼風格。
  • **索引是 JOIN 性命線**——被驅動表關聯列無索引 = 災難性性能。
  • MySQL 8.0+ 支持 LATERAL JOIN、CTE、窗口函數,複雜關聯查詢更優雅。
copyright © 萬盛學電腦網 all rights reserved