Oracle With Clause Syntax and Examples :

Oracle With Clause Syntax and Examples :

In this section i would like to explain the syntax as well as examples of With clause in oracle.Before checking the syntax and examples of With clause in oracle let us first check some important bullet points of With Clause :

With Clause in Oracle  is released in Oracle 9i release 2 to improve the performance of complex sql queries.

The clause works like a global temporary tables of oracle which is used to improve the query speed of complex sql queries.

This technique is also called as sub-query factoring as it is used to De-factor the subqueries.

With clause in oracle is not supported by all oracle versions ,the oracle version 9i and beyond versions.

When sub-query needs to be executed multiple times at that time With clause is used.

The name which is assigned to the sub-query is treated as though it was an inline view or table.

The With Clause is useful in Recursive queries as well.

WITH 

  items_costs AS (

    SELECT items_id, SUM(UNIT_COST) items_total

    FROM   STOCK_IN_DOCUMENTS_ITEMS e, STOCK_IN_DOCUMENTS d

    WHERE  e.document_id = d.document_id

         and e.STORES_ID  = d.STORES_ID

    GROUP BY items_id),

  avg_cost AS (

    SELECT SUM(items_total)/COUNT(*) avg

    FROM   items_costs)

SELECT *

FROM   items_costs

WHERE  items_total > (SELECT avg FROM avg_cost)

ORDER BY items_id;