MySQL 多表連接查詢實戰：別名技巧與自我連接模式解析

核心概念

• 連接查詢：多表關聯，支持同表多次連接（必須使用別名）
• 連接類型：INNER JOIN（內連接）、LEFT/RIGHT JOIN（外連接）

案例一：比賽結果表雙向關聯隊伍表

表結構

teams(t_id, t_name)
result(id, h_id, g_id, result, match_time)
-- h_id: 主隊ID, g_id: 客隊ID

目標輸出

主隊名 | 客隊名 | 比分 | 比賽時間

方法一：子查詢 + 連接（繁瑣）

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 t.t_id = r.h_id
) t1
JOIN (
  SELECT r.id, t.t_name AS g_name
  FROM result r JOIN teams t ON t.t_id = r.g_id
) t2 ON t1.id = t2.id;

方法二：多次連接同一表（優雅，推薦）

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，ON 條件分別匹配主隊/客隊 ID。

案例二：無限級分類自我連接

表結構

subject(id, name, pid)
-- pid: 父分類ID，頂級分類 pid=0 或 NULL

目標輸出

父分類名 | 子分類名

解決方案

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

核心模式：同一張表起兩個別名（p/c），通過 父表.主鍵 = 子表.外鍵 關聯。可遞歸擴展至多層級：

SELECT l1.name AS l1, l2.name AS l2, l3.name AS l3
FROM subject l1
LEFT JOIN subject l2 ON l1.id = l2.pid
LEFT JOIN subject l3 ON l2.id = l3.pid
WHERE l1.pid = 0; -- 只查頂級為根的三級分類

連接類型選擇指南

性能優化建議

1. 驅動表優先：小結果集驅動大表，或用 STRAIGHT_JOIN 固定順序
2. 被驅動表必須有索引：ON 條件列上建立索引
3. 避免 SELECT *：只查所需列，利用覆蓋索引
4. 大表連接慎用：百萬級以上考慮應用層組裝、緩存、異構同步到寬表

總結

多表連接的核心難點在於同表多次連接時的別名管理與連接類型的語義選擇。掌握「別名區分角色、ON 條件精準匹配、LEFT JOIN 保留主表」三大原則，即可輕鬆應對隊伍對陣、無限級分類、組織架構等經典關聯查詢場景。