Oracle 支援 ANSI SQL:1999 (SQL99) 與傳統 Oracle 語法兩套聯結寫法。本文系統整理等值/非等值/外部/自聯結、SQL99 新語法 (CROSS/NATURAL/USING/ON/INNER/OUTER)、子查詢分類與 Top-N 分析、Rownum 偽欄位常見誤區。
SELECT e.empno, e.ename, e.deptno, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
SELECT e.empno, e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;
-- 左外聯結:顯示所有 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 同用。SELECT e.ename AS emp, m.ename AS mgr FROM emp e, emp m WHERE e.mgr = m.empno;
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)] ...;
產生笛卡兒積,等同無 WHERE 條件的舊語法。
自動依所有同名欄位等值聯結,**不可** 加表前綴,同名欄位型態必須相容。
指定聯結欄位 (需同名):**不可** 加表前綴。
任意條件聯結,支援非同名欄位、複雜條件,**可** 加表前綴。
LEFT OUTER JOIN:左表全保留。RIGHT OUTER JOIN:右表全保留。FULL OUTER JOIN:雙表全保留 (Oracle 9i+ 支援)。SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE empno=9999); -- 子查詢無資料回傳 NULL,比較結果為 UNKNOWN,主查詢回傳 0 筆
-- 正確:先排序再取前 N SELECT * FROM ( SELECT * FROM emp ORDER BY sal DESC ) WHERE rownum <= 5;
rownum BETWEEN 5 AND 10 永無結果)。SELECT * FROM emp WHERE rownum >= 5; -- 0 筆 -- 原因:第一筆 rownum=1 不符合 >=5 被濾掉,下一筆重新從 1 開始,永無符合
SELECT * FROM (
SELECT a.*, rownum rn FROM (
SELECT * FROM emp ORDER BY sal DESC
) a WHERE rownum <= 10
) WHERE rn >= 6; -- 第 6-10 筆
SELECT * FROM emp ORDER BY sal DESC OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;