SQL WITH Clause

The SQL WITH Clause is used to create subquery block which can be referred in the main SQL statement many times. This clause has been introduced by the ORACLE from there 9i release2 version.

SQL WITH Clause Syntax

The syntax for SQL WITH Clause using single subquery alias is as follows:

WITH <alias_name> AS (sql_sub-query_statement)  
SELECT column_list FROM <alias_name> [table name]  
[WHERE <join_condition>];

When you use multiple subquery alias, the syntax will be as follows:

WITH <alias_name_A>  AS (sql_sub-query_statement)  
<alias_name_B> AS (sql_sub-query_statement_from_alias_name_A  
Or sql_sub-query_statement)  
SELECT <column_list>  
FROM <alias_name_A >,< alias_name_B >, [tablenames]  
[WHERE < join_condition>];