WITH Clause in Oracle Database

Imagine that you stuck in a situation where you need to create many views. One option you can have is start creating views right away and use them in your SQL. This will get your job done. But this approach may not be very efficient because you are unnecessarily creating views and storing them in database.

WITH clause can be a good option when you don't want to create and store views into the database. WITH clause are more like global temporary table or in other word they are called sub-query factoring. With clause helps you create views on the fly temporally and you can use those temp views in your subsequent SQL statement.

Here is an example of WITH clause in Oracle.

For our example purpose we are going to use with clause to calculate Avg and Max salary from employees table. There are many different ways you could calculate avg and max salary without the use of WITH clause but for now we are going to use it any way.

WITH

view1 as (Select * from employees),

view2 as (select AVG(Salary) Average from view1) ,

view3 as (Select MAX(Salary) Maximum from view1)

select Round(view2.average,2), view3.maximum from view2 cross join view3

As in the example above you can see that we created three views; view1 to retrieve data from employees table. view2 to calculate AVG(salary). Note that, for view2, we used view1 to calculate average. similarly for view3 we used view1 to find maximum salary.

Last line is select statement to show average salary and max salary, which were calculated in view2 and view3 respectively. Also, note that we are using cross join to join view2 and view3. Since most of us think that cross join should always be avoided, this can be a good example that cross join is useful in some typical situation.