A traditional SQL query can contain more than one SELECT from the same table
SELECT a, sum(b) FROM table_x HAVING sum (b) > (SELECT sum(b) FROM table_x)
To avoid scanning the table twice, we can define a WITH clause that will SELECT a, sum(b) from table_x.
The WITH clause must be defined before it is used in the query.
WITH clause Syntax:
WITH name_for_summary_data AS ( SELECT Statement) SELECT columns FROM name_for_summary_data WHERE conditions <=> ( SELECT column FROM name_for_summary_data) [ORDER BY columns]
The WITH clause is resolved internally as either an in-line view or a temporary
table (the CBO will choose).
The name_for_summary_data is visible to all elements of the query and subquery.
WITH MySummary AS ( SELECT dept_name, Sum(Salary) AS total_sal FROM emp, dept WHERE emp.dept_id = dept.dept_id GROUP BY dept_name) SELECT dept_name, total_sal FROM MySummary WHERE total_sal > ( SELECT SUM (total_sal) * 1/12 FROM MySummary) ORDER BY total_sal
The name_for_summary_data can be the same as an existing table name and will take precedence.
Oracle SQL Functions