SQL : Simplifying SQL using WITH Clause Part1
What is WITH Clause in Oracle?
What is CTE in Oracle?
How WITH clause helps in simplifying complex SQL?
Oracle With Clause is similar to temporary tables, where you store the data once and read it multiple
times in your SQL query.
Oracle With Clause is used when a sub-query is executed multiple times. In such cases sub-query will be
used as an inline view or a table.
In simple With Clause is used to simplify the complex SQL. You can improve the performance of the query
by using with clause
A WITH clause is really best used when the result of the WITH query is required more than once in the body
of the query such as where one averaged value needs to be compared against two or three times.
Syntax-
WITH (Alias) AS (Subquery-Select-SQL)
SELECT (Column-List) FROM (Alias), T1 Where T1.C1=(Alias).C1;
The WITH Query_Name clause lets you assign a name to a subquery block. You can then reference the subquery
block multiple places in the query by specifying the query name. Oracle optimizes the query by treating the
query name as either an inline view or as a temporary table.
WITH Clause Advantages
• Code Readability.
• The main advantage of the with clause (or subquery factoring clause) is that repeated references to
the subquery may be more efficient as the data is easily retrieved from the temporary table, rather
than being re-queried by each reference.
• Multiple reference to same table in a Query can be reduced by using the With clause, only single hit
will be given to the database and that result set can be used in the query multiple times so that the
cost of the query will be less when compared to using in-line view.
Home Page
https://tipsfororacle.blogspot.com/
#GoLearningPoint
