SQL Command- WITH Clause (CTE)

Rather writing jargon words I will go straight to the code:

WITH EMP_HIER(EMP_ID,MGR_ID,EMP_NAME)

AS

(Select

EMP_ID,MGR_ID,EMP_NAME

from V_Employee)

 

Select * from EMP_HIER

When I have seen the above code, first thing I have in my mind is; WITH clause is used to make derived table  ‘EMP_HIER’ by using a source table ‘V_Employee’. And it must be used instead of making volatile table.

And then tried to run only first part:

WITH EMP_HIER(EMP_ID,MGR_ID,EMP_NAME)

AS

(Select

EMP_ID,MGR_ID,EMP_NAME

from V_Employee)

 

Ya!! that was stupid move!! It doesn’t work separately; it works when select statement is there. So when I run whole code, then it works fine J

WITH EMP_HIER(EMP_ID,MGR_ID,EMP_NAME)

AS

(Select

EMP_ID,MGR_ID,EMP_NAME

from V_Employee)

Select * from EMP_HIER

Now next question came in my mind why I am going to use the above syntax, instead I can create volatile table. I did some investigation and share my thoughts with my colleagues and so far I found reason for using ‘WITH’ Clause is below:

1)     Data retrieve will not be slower

Explanation: 

When we make volatile table by getting data from other table then we generally don’t create table structure for volatile table. So your volatile table doesn’t have index automatically. Whereas if you use WITH clause then you are directly accessing to the source table so it will be faster than accessing to volatile table. 

 2)      Portability

Explanation: 

There is different syntax for making volatile in different database systems. E.g.

--In MS SQL:

CREATE TABLE #Temp1

( Name Char( 30 ), seqid integer )           

--In Teradata:

create volatile table V_Employee

(

EMP_ID int,

MGR_ID int,

EMP_NAME varchar(80)

)

on commit preserve rows;

But ‘WITH clause’ is same for both MS SQL and Teradata database. So you don’t need to change the syntax if your code needs to work for the both databases.

3)  Not enough pool space to create volatile table

Explanation: 

Sometimes you may not have enough pool space to create volatile table in your development environment and then you can use WITH clause to do the same.

In addition,  my study found, ‘WITH clause’ will be helpful when recursive data query will be needed e.g. find out different level of manager of a particular employee in a big organization. However, I will cover recursive queries in other post.

FYI, ‘WITH clause’ has official name which called Common Table Expression (CTE).