Oracle JOIN 全家族語法大全：8i/9i+ 新舊寫法對照、內外連接、全連接、笛卡爾積與效能優化

兩代語法體系

8i 舊式寫法速查 (僅供維護舊代碼參考)

-- 內連接
SELECT * FROM a, b WHERE a.id = b.id;

-- 左外連接 (保留 a 全部)
SELECT * FROM a, b WHERE a.id = b.id(+);

-- 右外連接 (保留 b 全部)
SELECT * FROM a, b WHERE a.id(+) = b.id;

-- 全外連接 (Union 組合)
SELECT * FROM a, b WHERE a.id = b.id(+)
UNION
SELECT * FROM a, b WHERE a.id(+) = b.id;

-- 笛卡爾積 (無連接條件)
SELECT * FROM a, b;

9i+ 標準 JOIN 語法全集

-- 1. 內連接 (僅匹配行)
SELECT * FROM a INNER JOIN b ON a.id = b.id;
-- 簡寫
SELECT * FROM a JOIN b ON a.id = b.id;

-- 2. 左外連接 (保留左表全部)
SELECT * FROM a LEFT JOIN b ON a.id = b.id;
-- 簡寫
SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.id;

-- 3. 右外連接 (保留右表全部)
SELECT * FROM a RIGHT JOIN b ON a.id = b.id;

-- 4. 全外連接 (保留雙方全部)
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.id;

-- 5. 交叉連接 (笛卡爾積，慎用)
SELECT * FROM a CROSS JOIN b;

實戰範例：兩表 A/B 驗證四種連接結果

多表連接順序與關聯條件放置陷阱

-- 錯誤：WHERE 條件混雜導致外連接退化為內連接
SELECT * FROM a LEFT JOIN b ON a.id = b.id
WHERE b.status = 'ACTIVE';  -- b 為 NULL 時 status 判斷失敗，等同內連接

-- 正確：外連接過濾條件放在 ON 子句
SELECT * FROM a LEFT JOIN b ON a.id = b.id AND b.status = 'ACTIVE';
-- 或子查詢預過濾
SELECT * FROM a LEFT JOIN (SELECT * FROM b WHERE status='ACTIVE') b ON a.id = b.id;

效能優化指南

1. 連接鍵建索引：驅動表與被驅動表連接欄位均需索引 (Nested Loop) 或統計資訊準確 (Hash Join)。
2. 小表驅動大表：`/*+ LEADING(small_table) USE_NL(large_table) */` 或依賴 CBO 統計資訊自動選擇。
3. 避免 `SELECT *`：僅選取必要欄位，減少投影、網路、排序開銷。
4. 大表關聯考慮分區裁剪：分區鍵參與連接條件，實現 Partition-Wise Join。
5. 統計資訊及時收集：`DBMS_STATS.GATHER_TABLE_STATS(..., CASCADE=>TRUE)`，否則 CBO 可能選錯連接方法/順序。

結語

**全面擁抱 ANSI JOIN 語法**：可讀性強、可移植、優化器支援最佳。舊式 `(+)` 僅在維護 8i 遺留系統時接觸。掌握四大連接語義、ON/WHERE 條件放置規則、多表連接順序控制，是編寫高效、正確 SQL 的基石。