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).

	WITH R1 AS
	(SELECT * FROM A JOIN B ON A.X = B.Y WHERE A.Z = 'abcd'
	) ,
	R2 AS
	(SELECT * FROM C WHERE D.X = 'pqrs' AND D.Y IN (SELECT Y FROM R1)
	) ,
	R3 AS
	(SELECT * FROM E JOIN R2 ON E.X = R2.Y WHERE E.Z = 'pqrs' AND R2.P ='bla bla'
	)
	SELECT R3.somthing,
	tab.something
	FROM R3,
	anotherTable tab
	WHERE THIS= 'THAT'
	ORDER BY R3.P

view raw with.sql hosted with ❤ by GitHub

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

	SELECT *
	FROM C
	WHERE D.X = 'pqrs'
	AND D.Y IN
	(SELECT Y FROM A JOIN B ON A.X = B.Y WHERE A.Z = 'abcd'
	)

view raw R2.sql hosted with ❤ by GitHub

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!

	WITH <sub query name> AS (SELECT something FROM aTable)
	SELECT another thing from <sub query name>

view raw syntax1.sql hosted with ❤ by GitHub

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.

	WITH <sub query name1> AS (SELECT something FROM aTable),
	<sub query name2> AS (SELECT somethingElse FROM anotherTable)
	SELECT anotherThing from <sub query name2>

view raw syntax2.sql hosted with ❤ by GitHub

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!