SQL WITH clause example

Question

How to use the WITH clause and what is the purpose of the WITH clause?

Cansomeone explain with a small example in detail ?

Answer 1

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]
[WHERE <join_condition>]