WITH Class

Definition:

In Oracle PL/SQL, the WITH clause is a subquery factoring clause which is used to create a named subquery block. This block acts as a virtual table or an inline view for a SQL statement. It was first introduced in Oracle 9.2. It reduces the overhead of multiple references of a table alias in a query. The scope of the WITH clause subquery block is the SELECT query with which is is associated.

Example Syntax:
With query_name As
(
SQL query
)
SELECT * FROM query_name;

--------------------------

WITH [NAME] as
(
SELECT statements
)
SELECT [column list]
FROM [NAME]

---------------------------

Example Usage:
SQL> WITH C AS
  (SELECT 1 N FROM DUAL UNION ALL
   SELECT 2 FROM DUAL UNION ALL
   SELECT 5 FROM DUAL UNION ALL
   SELECT 20 FROM DUAL UNION ALL
   SELECT 29 FROM DUAL UNION ALL
   SELECT 40 FROM DUAL)
   SELECT * FROM C;

         N
----------
         1
         2
         5
        20
        29
        40

6 rows selected.


Oracle With Clause Example

WITH CNT_DEPT AS
(
SELECT DEPARTMENT_ID,
COUNT(1) NUM_EMP
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
)
SELECT EMPLOYEE_ID,
SALARY/NUM_EMP
FROM EMPLOYEES E,
CNT_DEPT C
WHERE E.DEPARTMENT_ID = C.DEPARTMENT_ID;