Oracle 表連接操作完整指南：內連接、外連接、自我連接實戰

Oracle 表連接操作完整指南：內連接、外連接、自我連接實戰

連接類型概覽

• 內連接：僅返回滿足連接條件的行（等值連接）
• 外連接：返回主表所有行，從表無匹配時以 NULL 填充。分左、右、全外連接
• 自我連接：表與自身連接，處理層級關係（如員工-主管）

Oracle 傳統語法在 WHERE 條件中使用 (+) 標記外連接的「從表」側。

實戰範例

範例 1：內連接（等值連接）

SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno;

僅返回有部門的員工與有員工的部門（14 筆）。

範例 2：右外連接 — 顯示無員工的部門

SELECT * FROM emp e, dept d WHERE e.deptno(+) = d.deptno;

(+) 標記在 emp 側，表示 dept 為主表。部門 40（OPERATIONS）無員工亦會顯示，員工欄位為 NULL。

範例 3：左外連接 — 顯示無部門的員工

-- 先插入一筆無部門員工
INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(9999, 'test', 'CLERK', 7369, SYSDATE, 800, 100, NULL);

-- 左外連接：emp 為主表
SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno(+);

員工 9999 會顯示，部門欄位為 NULL。

範例 4：自我連接 — 員工與主管對照

SELECT e.empno, e.ename, e.mgr "員工主管編號", 
       m.empno "主管編號", m.ename "主管姓名"
FROM emp e, emp m
WHERE e.mgr = m.empno(+);

使用 (+) 確保頂層主管（KING，mgr 為 NULL）也能顯示，主管欄位顯示 NULL。

範例 5：綜合複雜查詢 — 1981 年入職員工完整報表

SELECT e.empno, e.ename, 
       TO_CHAR(e.hiredate, 'yyyy-mm-dd') 入職日期,
       e.job, e.sal 月基本薪資,
       (e.sal + NVL(e.comm, 0)) * 12 年總薪資,
       m.ename 主管姓名,
       DECODE(s.grade, 1, 'E等薪資', 2, 'D等薪資', 3, 'C等薪資', 4, 'B等薪資', 5, 'A等薪資') 薪資等級,
       d.deptno, d.dname, d.loc
FROM emp e, emp m, salgrade s, dept d
WHERE TO_CHAR(e.hiredate, 'yyyy') = '1981'
  AND e.mgr = m.empno(+)
  AND e.sal BETWEEN s.losal AND s.hisal
  AND e.deptno = d.deptno(+)
  AND e.sal BETWEEN 1500 AND 3500
ORDER BY 年總薪資 DESC, e.hiredate ASC;

涵蓋：自我連接（主管）、外連接（部門、主管）、非等值連接（薪資等級）、篩選、排序、計算欄位、日期格式化。

外連接 (+) 關鍵規則

建議

新專案優先採用 ANSI SQL:1999 標準語法（LEFT/RIGHT/FULL OUTER JOIN ... ON），語意明確、可移植、支援複雜條件組合。Oracle (+) 語法僅供維護舊系統參考。