Oracle 子查詢進階應用與集合操作實戰

Oracle 子查詢核心概念與實戰技巧

子查詢是 SQL 進階查詢的基石，廣泛應用於 WHERE、HAVING、DML 等子句。本文深入解析子查詢的嵌套規則、適用場景、集合運算與分頁查詢最佳實踐。

1. 子查詢嵌套結構

內層查詢先執行，結果集作為外層查詢的條件或資料來源：

SELECT * 
FROM (SELECT col1, col2 FROM table1 WHERE cond1) 
WHERE cond2;

2. 子查詢常用位置

• 條件不確定時：動態決定過濾值
• DML 語句中：INSERT/UPDATE/DELETE 的值來源
• HAVING 子句：分組後的聚合條件判斷

3. 集合運算 (UNION / MINUS / INTERSECT)

注意：參與運算的兩個查詢欄位數、類型必須一致。

4. 子查詢關鍵注意事項

1. 執行順序：由內而外，逐層執行
2. 非相關子查詢內層只執行一次
3. 若內層返回多行，不可使用 =, >, < 等單值比較，必須改用 IN、ANY、ALL 或 EXISTS

5. 實戰範例

範例一：找出最低平均薪資的職稱

SELECT title, AVG(salary)
FROM s_emp
GROUP BY title
HAVING AVG(salary) = (
    SELECT MIN(AVG(salary))
    FROM s_emp
    GROUP BY title
);

範例二：相關子查詢引用外層表

SELECT cty_name
FROM city
WHERE st_code IN (
    SELECT st_code
    FROM state
    WHERE st_name = 'TENNESSEE'
      AND city.cnt_code = state.cnt_code
);

說明：父查詢的 city.cnt_code 在子查詢中被引用，子查詢對每一行父查詢結果執行一次 (相關子查詢)。

6. 高效分頁查詢 (取第 81~100 筆)

Oracle ROWNUM 只能用 <=，不能直接用 >。標準寫法：

SELECT *
FROM (
    SELECT ROWNUM AS rn, t.*
    FROM (
        SELECT field_list
        FROM table_name
        WHERE cond1
        ORDER BY cond2
    ) t
    WHERE ROWNUM <= 100
)
WHERE rn > 80
ORDER BY cond3;

此寫法相容 Oracle 10g 以上版本，12c 可改用 OFFSET ... FETCH 語法更簡潔。