I was trying to understand how to use the
WITH clause and the purpose of the
All I understood was, the
WITH clause was a replacement for normal sub-queries.
Can anyone explain this to me with a small example in detail ?
This question is tagged with
~ Asked on 2012-09-23 12:14:37
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 sub-query alias.
WITH <alias_name> AS (sql_subquery_statement) SELECT column_list FROM <alias_name>[,table_name] [WHERE <join_condition>]
When using multiple sub-query aliases, the syntax is as follows.
WITH <alias_name_A> AS (sql_subquery_statement), <alias_name_B> AS(sql_subquery_statement_from_alias_name_A or sql_subquery_statement ) SELECT <column_list> FROM <alias_name_A>, <alias_name_B> [,table_names] [WHERE <join_condition>]
In the syntax documentation above, the occurrences of
alias_name is a meaningful name you would give to the sub-query after the AS clause. Each sub-query should be separated with a comma Example for WITH statement. The rest of the queries follow the standard formats for simple and complex SQL SELECT queries.
For more information: http://www.brighthub.com/internet/web-development/articles/91893.aspx
~ Answered on 2012-09-23 12:20:01