Oracle WITH clause- details with simple explanations

Oracle 9.2 brought up a feature to write sub queries using WITH clause, formally known as Subquery factoring. Using this keyword, it is possible to define sub queries in a complex SQL query which can be reused several times.You might wonder why do we need a special keyword yet again and why we can't manage with the traditional sub query mechanism. This blog tries to answer these questions with simple, yet powerful examples.

In a nutshell, WITH clause give the following advantages for developers who deal with complex queries:

• It simplifies the complex query
• Improves readability of the query
• Helps to reuse a code segment without repeating unnecessarily
• Makes a developer's life easy when comes to debugging/ finding bugs in the query

Let's stat off with a simple example of this before look at the explanations of the syntax. Hope this query will light up the real advantages achieved through subquery factoring (WITH clause).

For simplicity, I've used some A, B, bla bla. Hope it helps to get the message easily! As you can see, it looks nice to read and understand; It has simplified the complex query by just dividing it into pieces. Imagine how it would be with the traditional sub query?

Just take R2 for instance. If we try to get the output of it using the traditional sub queries, it'll look like



We know how simple this query is. Just imagine the size of the total query if we are writing some real complex query!

So let's see the syntax of this now. Hope you got it right already by going through the above simple example. Syntax is simple as follows. If you understand like a sentence in English, you'll never forget it!



Similarly, when you have multiple sub queries, it's like- let A AS this, B AS that. Then just write a select query to get your desired columns.

Pls note this will work with Oracle 9.2 or later versions only. Hope this helped you guys, will catch u with another interesting topic soon!