Oracle WITH Clause.

For more info visit

WITH clause is also known as Subquery factoring clause. It is used to simplify the structure of complex queries, and in some cases, optimize the queries.

With clause works in the same way as a Temporary table or an inline view, but the main difference between With clause and, an inline view or a Temporary table is that, a query written using With clause is less complex and easier to understand.

When we use a With clause, it depends on the optimizer whether it will make temporary table or an inline view, corresponding to the subquery in the With clause. This also depends on the subqueries used in With clause, if the queries are too complex then the optimizer may create Temporary tables ,else an inline view is sufficient.

We can also pass Optimizer hints in the subqueries written in with clause, to request the optimizer for an inline view or a Temporary table.

Select /*+ MATERIALIZE */ .... For Temporary table

Select /*+ INLINE */ ..... For Inline view

Now let's see an example, with and without With clause.

Suppose I want to find out the names of the employees having salary greater than the average salary of the department in which they work.

I can write this query in 3 ways.

1) Using Corelated Subqueries.

select ed1.name from employee_details ed1
where salary>(select avg(salary) from
employee_details ed2 where
ed1.department=ed2.department)

Writing corelated subquery is fine when data in the tables is less, but when there is a large amount of data in tables then this approach may not be suitable. For each employee in the table, the subquery will execute. So, we discard this approach in case of huge amount of data in tables.

2) The second approach is to create Inline view.

select ed1.name from employee_details ed1,
(Select avg(salary) sal,department
from employee_details group by
department) ed2 where ed2.department=ed1.department
and ed1.salary > ed2.sal

Here, an inline view is created, which is more efficient in case of huge amount of data. The query given above contains only one inline view, but what about the queries containing many inline views. It will become very complex to understand.

3) Using With Clause.

There is not much difference between aWith clause and an inline view, except the way of writing queries becomes more understandable.

Now, let's see the same query using a With Clause.

With ed2 as(Select avg(salary) sal,department
from employee_details group by
department)
select ed1.name from employee_details ed1,
ed2
where ed1.department=ed2.department and
ed1.salary>ed2.sal

The queries using With clause starts with With instead of Select, which may seem wierd at first, but as you start using With Clause, it will become inseparable part of  writing simple and understandable queries.

With clause may come handy in a lot of other situations(demanding subqueries), the only limit is how efficiently we can reduce the complexity of our queries using With Clause.