Oracle introduced the SQL WITH clause in the Oracle 9i release 2 database. The SQL WITH clause enables you to give a sub-query block a name (a process known as sub-query refactoring) that may be referred from several locations within the main SQL query. The name given to the sub-query is processed as if it were a table or inline view. The WITH clause in SQL is essentially a drop-in replacement for the sub-query.
The SQL WITH Clause Syntax
When utilising a single sub-query alias, the SQL WITH clause has the following syntax.
WITH <alias_name> AS (sql_subquery_statement)
SELECT column_list FROM <alias_name>[,table_name]