Oracle With Clause :

In my previous article i have given the different SQL tutorials with real life examples.In this article i will give you the Oracle With Clause information with its examples. You might have seen that so many reporting tool queries starts with Oracle With Clause.The With Clause is mainly used to improve the performance of the specific query.The Oracle 9i provides the new functionality of With Clause.The with clause is just like temporary tables in oracle.Mainly the with clause is used to execute subqueries in fast way.It is called as sub-query factoring.

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 :

1. With Clause in Oracle  is released in Oracle 9i release 2 to improve the performance of complex sql queries.
2. The clause works like a global temporary tables of oracle which is used to improve the query speed of complex sql queries.
3. This technique is also called as sub-query factoring as it is used to De-factor the subqueries.
4. With clause in oracle is not supported by all oracle versions ,the oracle version 9i and beyond versions.
5. When sub-query needs to be executed multiple times at that time With clause is used.
6. The name which is assigned to the sub-query is treated as though it was an inline view or table.
7. The With Clause is useful in Recursive queries as well.

These are some important points of With Clause in oracle. In following section i would like to explain about the syntax as well as multiple examples of with clause.

Syntax :

With SQL_Query_Name As

(

SQL query;

)

Select * from New_SQL_Query_name;

 

Execution of With Clause :

In this section i would like to explain about the step by step execution of With clause.

Step 1 : The SQL Query within the with clause is executed at first step.

Step 2 : The output of the SQL query is stored in to temporary relation of with clause.

Step 3 : The Main query is executed with temporary relation produced at last stage.

With Clause with Aggregation :

In this section i would like to give you example with its syntax for With clause with aggregation.There are so many times the With clause is used in oracle .

Syntax :

WITH
sub-query_name
AS
(The aggregation SQL statement)
SELECT
(query name sub-query_name);

Real life example 1 :

Let us take the example of Student table.If user wants to find out the Students from Student table whose marks is more than Average of all marks.

With Student_Temp(Average_Marks)

As

(Select Avg(Marks) From Student),

Select Roll_No,Name,Marks From Student , Student_Temp

Where Student.Marks > Student_Temp.Average_Marks);

The above example will give you the Students whose marks are greater than average marks with using With Clause with Aggregation.

Real Life Example 2 :

There are some situations where user needs to calculate Salary of the Employee with Total number of the Employees and user needs to show it department-wise then following query is useful.

With Department_Count As

(

Select Dept_No,Count(Emp_No) As ‘No_Of_Employees’

From Employee

Group by Dept_No

)

Select Employee_No,

Salary/No_Of_Employees

From Employee E,

Department_Count C

Where E.Dept_no = C.Dept_No;

Points to Remember :

In this section, I would like to give some important points to remember while using With Clause in Oracle.

Point 1 :

The With clause in Oracle can be beneficial for complex SQL queries. For Simple SQL queries dont use with clause to make it complicated.

Point 2 :

The with clause fragments the complex subqueries in to queries.It is used for simple debugging of the query.

Point 3 :

The With clause in oracle is nothing but the drop -in replacement for normal subqueries.

Point 4 :

When User find the sub-query and needs to improve the performance ; Try to use With clause and convert that sub-query in to With clause query to improve the performance.

These are above some most important points to remember while using With clause in Oracle. I hope this article will be useful for users. If you like this article or if you have any concerns with the same kindly comment in to comments section.