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 [NAME] as
(
SELECT statements
)
SELECT [column list]
FROM [NAME]
Example Usage:
The SQL statement below uses WITH clause to declare a virtual table C and has a SELECT query on it.
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.
Related Links:
Related Code Snippets: