MySQL 連接查詢經典例題：多表關聯、自我關聯與別名實戰技巧

MySQL 連接查詢經典例題：多表關聯、自我關聯與別名實戰技巧

JOIN 是關聯式資料庫查詢的靈魂。本文通過兩個經典例題——比賽場次查詢（多表多次 JOIN）與分類樹查詢（自我關聯）——展示 INNER JOIN、表別名、子查詢混合等核心技巧，並對比兩種寫法的優劣。

例題一：比賽場次 — 主隊、客隊、比分、時間

資料表

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
2  | 2          | 3          | 1:1    | 2024-01-02

目標輸出

id | h_name (主隊) | g_name (客隊) | result | match_time
---|---------------|---------------|--------|-----------
1  | Team A        | Team B        | 2:1    | 2024-01-01
2  | Team B        | Team C        | 1:1    | 2024-01-02

方法一：子查詢 + JOIN（繁瑣但邏輯清晰）

-- 步驟 1：取主隊名
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;

-- 步驟 2：取客隊名
SELECT r.id, t.t_name AS g_name
FROM result r JOIN teams t ON t.t_id = r.g_id;

-- 步驟 3：按 id 合併
SELECT t1.id, t1.h_name, t2.g_name, t1.result, t1.match_time
FROM (步驟1) t1 JOIN (步驟2) t2 ON t1.id = t2.id;

方法二：多次 JOIN 同一張表（簡潔高效，必須用別名）

SELECT
  r.id,
  t1.t_name AS h_name,
  t2.t_name AS g_name,
  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;

優勢：單條 SQL、執行計畫優化器易處理、可讀性強。關鍵：同表多次 JOIN 必須給不同別名 (t1, t2)。

例題二：分類樹 — 父欄目名、子欄目名

資料表 subject

id | name       | pid (父ID)
---|------------|-----------
1  | Electronics| 0
2  | Phone      | 1
3  | Laptop     | 1
4  | Accessory  | 2

目標輸出

p_name (父欄目) | son_name (子欄目)
----------------|-----------------
Electronics     | Phone
Electronics     | Laptop
Phone           | Accessory

自我關聯查詢

SELECT
  t1.name AS p_name,
  t2.name AS son_name
FROM subject t1
JOIN subject t2 ON t1.id = t2.pid;

同一張表以不同角色（父/子）參與 JOIN，別名 t1/t2 是區分身份的關鍵。

核心技巧總結

效能建議

• 關聯欄位 (h_id, g_id, pid) 務必建立索引。
• 大表 JOIN 小表時，小表放左邊（驅動表），或讓優化器自動選擇（EXPLAIN 驗證）。
• 避免在 ON 條件中使用函數/運算，導致索引失效。

熟練掌握「多次 JOIN 同表需別名」、「自我關聯模擬樹結構」兩大模式，即可應對 80% 以上的業務關聯查詢場景。