您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 多表聯結查詢完整指南：內聯結、外聯結、自聯結、聯結技巧與執行計畫分析

編輯：Oracle 資料庫基礎

多表聯結是關聯式資料庫核心操作。本文系統整理 Oracle 聯結語法 (舊式 + 新式 ANSI)、四大聯結類型、聯結條件撰寫技巧、效能優化原則、執行計畫分析工具 (AUTOTRACE/PLAN_TABLE)，助您寫出高效、可維護的多表查詢。

聯結語法演進

舊式 Oracle 語法 (WHERE 子句)


-- 內聯結
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- 外聯結 (+ 號在缺少資料的一方)
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);  -- 顯示無部門員工

ANSI SQL:1999 標準語法 (建議使用)


-- 內聯結
SELECT e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- 左外聯結
SELECT e.last_name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

-- 右外聯結
SELECT e.last_name, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;

-- 全外聯結
SELECT e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

-- 交叉聯結 (笛卡兒積)
SELECT * FROM employees CROSS JOIN departments;

四大聯結類型對照

類型	關鍵字	結果集	適用場景
內聯結	INNER JOIN / JOIN	雙方皆符合條件的列	主流業務查詢
左外聯結	LEFT [OUTER] JOIN	左表全保留 + 右表匹配	主表全保、從表可選
右外聯結	RIGHT [OUTER] JOIN	右表全保留 + 左表匹配	較少用，可改 LEFT
全外聯結	FULL [OUTER] JOIN	雙方全保留	對帳、差異比對
自聯結	表別名自我 JOIN	同一表不同角色	階層結構、找重複

聯結條件分類

等值聯結：= (最常用，可用 Hash Join/Merge Join)。
非等值聯結：BETWEEN ... AND、>、<、!= (通常用 Nested Loops)。
複合條件：多欄位聯結 ON e.emp_id = m.emp_id AND e.dept_id = m.dept_id。

實戰範例

1. 刪除重複 Email (自聯結 + ROWID)


DELETE FROM t1
WHERE rowid > (
  SELECT MIN(rowid) FROM t1 t2
  WHERE t1.email = t2.email
  GROUP BY email
  HAVING COUNT(*) > 1
);

2. 手機號碼歸屬查詢 (多表 LEFT JOIN)


SELECT a.handphoneno,
       NVL(c.name, 'NULL') AS operator_name,
       a.totalscore
FROM topscore a
LEFT JOIN chargeoperator cc ON SUBSTR(a.handphoneno,1,7) = cc.hpnohead
LEFT JOIN chargeoperatorinfo c ON cc.chargetype = c.chargetype
ORDER BY a.totalscore DESC;

聯結效能優化五大原則

聯結 N 表至少 N-1 個聯結條件，避免笛卡兒積。
聯結欄位建立索引 (FK 欄位必索引)，優先整數型別。
外聯結時避免在 WHERE 使用 OR/IN，會導致外聯結失效轉內聯結。
小表驅動大表：Nested Loops 適合小結果集驅動；Hash Join 適合大表等值聯結。
善用 WITH 子查詢 (CTE) 簡化複雜聯結邏輯，提高可讀性並助最佳化器轉換。

執行計畫分析標準流程


-- 1. 建立 PLAN_TABLE (SYS 執行)
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
-- 2. 授權 PLUSTRACE 角色 (SYS)
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
GRANT plustrace TO username;

-- 3. 一般用戶開啟 AUTOTRACE
SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
-- 執行查詢
SELECT ... FROM ... JOIN ... WHERE ...;
-- 關閉
SET AUTOTRACE OFF