WITH子句：子查询命名

WITH子句是SQL-99标准的内容，在ORACLE9.2中被引入。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子句中。

综上所述，该子句主要用来简化查询，增强语句可读性，提高查询效率。