Oracle 自連接查詢實戰：同一表多別名解決層級關係

善用表別名破解階層式資料查詢難題

自連接 是查詢同一張表內部關聯關係 (如員工-主管、站點-路線) 的關鍵技巧。本文透過公車路網實例，詳細說明自連接語法、別名機制、多種連接條件語意差異與換乘查詢進階應用。

核心概念：表別名機制

自連接時，同一張表在查詢中出現多次，必須給予不同別名以區分實體：

FROM route R1, route R2  -- R1、R2 為 route 表的兩個實體別名
WHERE R1.num = R2.num    -- 透過別名.欄位 參考對應實體欄位

實戰範例：愛丁堡公車路網

資料結構

• stops(id, name)：車站表
• route(num, company, pos, stop)：路線表 (num=路線號, company=業者, pos=順序, stop=站點ID)

案例一：同一路線的任意兩站連通查詢

SELECT * FROM route R1, route R2
WHERE R1.num = R2.num
  AND R1.company = R2.company;

語意：找出同一業者、同一路線號的所有站點組合，可推導任兩站直達可行性。

案例二：共用站點的路線換乘查詢

SELECT * FROM route R1, route R2
WHERE R1.stop = R2.stop;

語意：找出停靠相同站點的所有路線組合，是換乘分析基礎。

案例三：指定兩站之間的直達/換乘路線

SELECT R1.company, R1.num
FROM route R1, route R2, stops S1, stops S2
WHERE R1.num = R2.num
  AND R1.company = R2.company
  AND R1.stop = S1.id
  AND R2.stop = S2.id
  AND S1.name = 'Craiglockhart'
  AND S2.name = 'Tollcross';

語意：查詢從 Craiglockhart 到 Tollcross 可直達的路線 (同路線號、同業者)。若需換乘，再增加中轉站條件。

自連接設計模式總結

效能與寫法建議

• 優先使用 ANSI JOIN 語法：FROM route R1 JOIN route R2 ON R1.num = R2.num，可讀性更佳
• 連接欄位建立索引 (如 route(num, company)、route(stop)) 顯著提升效能
• 避免笛卡兒積：確保連接條件足夠選擇性，必要時加入過濾條件縮小結果集
• 複雜層級查詢考慮 CONNECT BY (Oracle 階層查詢) 或遞迴 CTE (11gR2+)

掌握自連接別名邏輯，能靈活表達同一實體集合內部的豐富關聯語意。