MySQL 多表關聯查詢實戰：自關聯、別名技巧與經典賽程/分類遞歸例題

MySQL 多表關聯查詢實戰：自關聯、別名技巧與經典賽程/分類遞歸例題

JOIN 是關係型資料庫最核心威力。MySQL 支援多表連接、同表多次連接（必須用別名）、各類外連接。本文通過兩個經典例題（賽程表、分類遞歸）演示從「子查詢混合」到「多次 JOIN」的優化思路。

例題 1：賽程表 — 查主隊、客隊、比分、時間

表結構

-- teams
t_id | t_name
1    | Team A
2    | Team B
3    | Team C

-- result
id | h_id | g_id | result | match_time
1  | 1    | 2    | 2:1    | 2024-01-01 15:00
2  | 2    | 3    | 1:1    | 2024-01-02 15:00

方法一：子查詢 + 連接 (繁瑣)

SELECT t1.id, t1.h_name, t2.g_name, t1.result, t1.match_time
FROM (
  SELECT r.id, t.t_name AS h_name, r.match_time, r.result
  FROM result r JOIN teams t ON r.h_id = t.t_id
) t1
JOIN (
  SELECT r.id, t.t_name AS g_name
  FROM result r JOIN teams t ON r.g_id = t.t_id
) t2 ON t1.id = t2.id;

方法二：多次 JOIN 同一張表 (推薦，清晰高效)

SELECT r.id,
       t1.t_name AS home_team,
       t2.t_name AS guest_team,
       r.result,
       r.match_time
FROM result r
JOIN teams t1 ON r.h_id = t1.t_id
JOIN teams t2 ON r.g_id = t2.t_id;

關鍵點：teams 表出現兩次，必須別名 (t1/t2) 區分主隊/客隊，否則列名衝突報錯。

例題 2：分類表自關聯 — 查父分類與子分類

表結構

subject
id | name      | pid
1  | Electronics | 0
2  | Phones      | 1
3  | Laptops     | 1
4  | iPhone      | 2
5  | Android     | 2

自連接查詢父子關係

SELECT p.name AS parent_name, c.name AS child_name
FROM subject p
JOIN subject c ON p.id = c.pid;

結果：

parent_name  | child_name
Electronics  | Phones
Electronics  | Laptops
Phones       | iPhone
Phones       | Android

自連接必須別名 (p/c)，否則 id/pid 無法區分父子層級。

進階：遞歸查詢所有層級 (MySQL 8.0 CTE)

WITH RECURSIVE cat_tree AS (
  SELECT id, name, pid, 1 AS lvl, name AS path
  FROM subject WHERE pid = 0
  UNION ALL
  SELECT s.id, s.name, s.pid, ct.lvl + 1, CONCAT(ct.path, ' > ', s.name)
  FROM subject s JOIN cat_tree ct ON s.pid = ct.id
)
SELECT * FROM cat_tree ORDER BY path;

輸出完整層級路徑，支援無限級分類。

JOIN 類型速查

最佳實踐清單

1. 永遠給表起別名 ( t1、u、p )，SQL 可讀性大增，同表多連時更是強制。
2. ON 放連接條件，WHERE 放過濾條件：外連接時 WHERE 會把外連接退化為內連接。
3. 驅動表選小表：JOIN 順序影響優化器選擇，小結果集驅動大表通常更快。
4. 覆蓋索引：被連接列 + 查詢列建立聯合索引，避免回表。
5. 避免 SELECT *：明確列出所需列，減少網路傳輸與緩衝池壓力。

掌握別名與多次 JOIN，即可優雅解決「同表多角色」、「遞歸層級」等高頻業務場景。