WITH子句：子查詢命名

WITH子句是SQL-99標準的內容，在Oracle9.2中被引入。WITH子句可以用來命名子查詢。當子查詢在多個地方被使用時，可以直接使用查詢名。該子句命名的子查詢會被最佳化器當成內聯視圖或暫存資料表對待。後一種情況可以提高查詢效率。
 
使用SCOTT模式，對於每名員工取得他所在部門的人數，使用內聯視圖可以實現如下：
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc
WHERE  e.deptno = dc.deptno;
 
使用WITH子句，可以實現如下：
WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e,
       dept_count dc
WHERE  e.deptno = dc.deptno;
 
需求稍微變得複雜，現在在取得每名員工所在部門人數的同時，還要取得該員工的經理及其經理所在部門的人數。使用內聯視圖實現如下：
SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc1,
       emp m,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc2
WHERE  e.deptno = dc1.deptno
AND    e.mgr = m.empno
AND    m.deptno = dc2.deptno;
 
使用WITH子句實現如下：
WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       dept_count dc1,
       emp m,
       dept_count dc2
WHERE  e.deptno = dc1.deptno
AND    e.mgr = m.empno
AND    m.deptno = dc2.deptno;
 
顯然，使用WITH子句結構更簡潔，使用內聯視圖時出現兩次的子查詢，在使用WITH子句時只出現一次。
 
在沒有重複子查詢出現的情況下，也可以使用WITH子句簡化複雜查詢。下面的例子列出了所有開支大於平均開支的部門。
WITH
  dept_costs AS (
    SELECT dname, SUM(sal) dept_total
    FROM   emp e, dept d
    WHERE  e.deptno = d.deptno
    GROUP BY dname),
  avg_cost AS (
    SELECT SUM(dept_total)/COUNT(*) avg
    FROM   dept_costs)
SELECT *
FROM   dept_costs
WHERE  dept_total > (SELECT avg FROM avg_cost)
ORDER BY dname;
 
上面的查詢，主體部分很簡單，複雜的邏輯隱藏在了WITH子句中。
 
綜上所述，該子句主要用來簡化查詢，增強語句可讀性，提高查詢效率。