How-to: Analytic Features - WITH clause

A traditional SQL query can contain more than one SELECT from the same table such as
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.

Example:

  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

Notes:
The name_for_summary_data can be the same as an existing table name and will take precedence.

Related

Analytic features
Oracle SQL Functions