WITH clause: subquery name

The WITH clause is the content of the SQL-99 standard and is introduced in Oracle9.2. The WITH clause can be used to name subqueries. When a subquery is used in multiple places, you can directly use the query name. Subqueries named by this clause are treated as inline views or temporary tables by the optimizer. In the latter case, query efficiency can be improved.
 
Using the SCOTT mode, you can use the inline view for each employee to obtain the number of persons in his/her department as follows:
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;
 
The WITH clause can be implemented as follows:
WITH dept_count (
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;
 
The demand is a little complicated. Now, while obtaining the number of employees in each department, we also need to obtain the number of managers and their departments. The Inline view is implemented as follows:
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;
 
Use the WITH clause to implement the following:
WITH dept_count (
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;
 
Obviously, the use of the WITH clause structure is more concise. When using the inline view, two subqueries appear, and only one when using the WITH clause.
 
You can use the WITH clause to simplify complex queries when no duplicate queries exist. The following example lists all departments whose expenditures are greater than the average.
WITH
Dept_costs (
SELECT dname, SUM (sal) dept_total
FROM emp e, dept d
WHERE e. deptno = d. deptno
Group by dname ),
Avg_cost (
Select sum (dept_total)/COUNT (*) avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total> (SELECT avg FROM avg_cost)
Order by dname;
 
In the preceding query, the subject is very simple, and complicated logic is hidden in the WITH clause.
 
To sum up, this clause is mainly used to simplify queries, enhance statement readability, and improve query efficiency.