萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 多表聯結與子查詢完整教學:內/外/自/交叉/自然聯結、Top-N 分析、Rownum 偽欄位陷阱

Oracle 支援 ANSI SQL:1999 (SQL99) 與傳統 Oracle 語法兩套聯結寫法。本文系統整理等值/非等值/外部/自聯結、SQL99 新語法 (CROSS/NATURAL/USING/ON/INNER/OUTER)、子查詢分類與 Top-N 分析、Rownum 偽欄位常見誤區。

傳統 Oracle 聯結語法 (WHERE 子句)

等值聯結 (Equijoin)

SELECT e.empno, e.ename, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

非等值聯結 (Non-Equijoin)

SELECT e.empno, e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

外部聯結 (Outer Join) - (+) 運算子

-- 左外聯結:顯示所有 dept,無員工者員工欄位為 NULL
SELECT d.deptno, d.dname, e.ename
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno;

-- 右外聯結:顯示所有 emp,無部門者部門欄位為 NULL
SELECT e.ename, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno(+);
  • (+) 標示在「缺少資料」的一方。
  • 同一條件只能出現一個 (+),不可與 OR/IN 同用。

自聯結 (Self Join)

SELECT e.ename AS emp, m.ename AS mgr
FROM emp e, emp m
WHERE e.mgr = m.empno;

SQL99 標準聯結語法 (FROM 子句)

SELECT ...
FROM table1
[CROSS JOIN table2]
[NATURAL JOIN table2]
[JOIN table2 USING (col)]
[JOIN table2 ON (table1.col = table2.col)]
[LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.col = table2.col)]
...;

交叉聯結 (CROSS JOIN)

產生笛卡兒積,等同無 WHERE 條件的舊語法。

自然聯結 (NATURAL JOIN)

自動依所有同名欄位等值聯結,**不可** 加表前綴,同名欄位型態必須相容。

USING 子句

指定聯結欄位 (需同名):**不可** 加表前綴。

ON 子句

任意條件聯結,支援非同名欄位、複雜條件,**可** 加表前綴。

外部聯結 (OUTER JOIN)

  • LEFT OUTER JOIN:左表全保留。
  • RIGHT OUTER JOIN:右表全保留。
  • FULL OUTER JOIN:雙表全保留 (Oracle 9i+ 支援)。

子查詢 (Subquery)

分類

  • 單列子查詢:回傳 1 列 1 欄,可用單列比較運算子 (=, >, <, <>...)。
  • 多列子查詢:回傳多列,必須用多列比較運算子 (IN, ANY, ALL, EXISTS)。

常見陷阱:NULL 值

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE empno=9999);
-- 子查詢無資料回傳 NULL,比較結果為 UNKNOWN,主查詢回傳 0 筆

Top-N 分析 (利用內聯視圖 + ROWNUM)

-- 正確:先排序再取前 N
SELECT * FROM (
  SELECT * FROM emp ORDER BY sal DESC
) WHERE rownum <= 5;

Rownum 偽欄位深度解析

特性

  • 查詢結果集**產生時**依序編號 (1-based)。
  • **非** 儲存於表中,不可用於 WHERE 篩選範圍 (如 rownum BETWEEN 5 AND 10 永無結果)。

錯誤示範

SELECT * FROM emp WHERE rownum >= 5;  -- 0 筆
-- 原因:第一筆 rownum=1 不符合 >=5 被濾掉,下一筆重新從 1 開始,永無符合

正確分頁查詢 (11g 及之前)

SELECT * FROM (
  SELECT a.*, rownum rn FROM (
    SELECT * FROM emp ORDER BY sal DESC
  ) a WHERE rownum <= 10
) WHERE rn >= 6;  -- 第 6-10 筆

12c+ 簡化分頁

SELECT * FROM emp ORDER BY sal DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
copyright © 萬盛學電腦網 all rights reserved