The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database. The SQL WITH clause allows you to give a sub-query block a name a process also called sub-query refactoring, which can be referenced in several places within the main SQL query. The name assigned to the sub-query is treated as though it was an inline view or table. The SQL WITH clause is basically a drop-in replacement to the normal sub-query.
Syntax For The SQL WITH Clause
The following is the syntax of the SQL WITH clause when using a single subquery alias.
WITH
AS (sql_subquery_statement)
SELECT column_list FROM [,tablename]
[WHERE ]
When using multiple subquery aliases, the sysntax is as follows.
WITH
AS (sql_subquery_statement)
AS(sql_subquery_statement_from_alias_name_A
or sql_subquery_statement )
SELECT
FROM , , [tablenames]
[WHERE ]
