Oracle SELECT 進階查詢技巧全攻略

從基礎語法到進階分析函數的 SELECT 實戰指南

SELECT 是 SQL 最核心指令，本文系統整理欄位運算、函數應用、子查詢、集合運算、分組、多表連接、排序與模糊比對等高頻用法。

1. 欄位運算與常用函數

• 算術運算：(col1 * col2) / 3
• 聚合函數：COUNT(*), MIN(), MAX(), AVG(), DISTINCT
• 日期格式化：TO_CHAR(date_col, 'YYYY-MM-DD HH24:MI:SS')
• NVL(expr1, expr2)：expr1 為 NULL 時回傳 expr2
• DECODE(col, val1, ret1, val2, ret2, ..., default)：類似 CASE WHEN
• LPAD(str, len, pad_str)：左補字元至指定長度

2. 子查詢與集合運算

-- 巢狀子查詢
SELECT * FROM (SELECT ... FROM t1 WHERE cond1) WHERE cond2;

-- 集合運算
SELECT ... FROM t1 UNION SELECT ... FROM t2;      -- 去重
SELECT ... FROM t1 UNION ALL SELECT ... FROM t2;  -- 保留重複
SELECT ... FROM t1 MINUS SELECT ... FROM t2;      -- 差集
SELECT ... FROM t1 INTERSECT SELECT ... FROM t2;  -- 交集

3. 分組與 HAVING

SELECT col1, col2, COUNT(*)
FROM t1, t2
WHERE join_cond
GROUP BY col1, col2
HAVING COUNT(*) > 5;

4. 多表連接 (舊式 vs ANSI)

-- Oracle 舊式外部連接 (+)
SELECT * FROM t1, t2 WHERE t1.id = t2.id(+);  -- LEFT JOIN

-- ANSI 標準 (9i+ 推薦)
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id;
SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id;
SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id;

(+) 只能出現在單邊，且不可與 OR 混用，ANSI 語法更清晰且功能完整。

5. 排序與模糊比對

• ORDER BY col1 ASC, col2 DESC (預設 ASC)
• INSTR(col, 'str') > 0 或 col LIKE '%str%'
• 每列隱含 ROWID 唯一標識物理位置

6. 實用技巧摘要

熟練這些模式，能解決 90% 以上的業務查詢場景。