SQL WITH clause example

Question

I was trying to understand how to use the WITH clause and the purpose of the WITH clause   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

User · Answer

This has been fully answered here    See Oracle s docs on SELECT to see how subquery factoring works  and Mark s example   WITH employee AS  SELECT   FROM Employees  SELECT   FROM employee WHERE ID  lt  20 UNION ALL SELECT   FROM employee WHERE Sex    M

User · Answer

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  lt alias name gt  AS  sql subquery statement  SELECT column list FROM  lt alias name gt   table name   WHERE  lt join condition gt     When using multiple sub-query aliases  the syntax is as follows   WITH  lt alias name A gt  AS  sql subquery statement    lt alias name B gt  AS sql subquery statement from alias name A or sql subquery statement   SELECT  lt column list gt  FROM  lt alias name A gt    lt alias name B gt    table names   WHERE  lt join condition gt     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

[sql] SQL WITH clause example

Examples related to sql

Examples related to with-clause