Tuesday, 3 September 2013

CTE ( Common Table expression ) in SQL Server

Its very new concept for me, heard from Mr.Vinayak Sir.
Thanks to them.

CTE (Common Table Expression ) is nothing but return same result as SELECT query will do with the help of JOINS,UNION etc.

It allows you to define the subquery at once, name it using an alias and later call the same data using the alias just like what you do with a normal table.

Take a look at following difference between plain query with join and CTE query

Plain SQL query :

Its plain query.




CTE query :

Using CTE syntax/format , we define query as follows,

Syntax :

With [table_aliesname] ( userDefinedColumn1,userDefinedColumn2,userdefinedcolumnN )
As
(
[Plain SQL select query.]
)
-- If you want to join more table only need to give ',' comma for closing round bracket and continue as same as T2 table in Examle

 Regards,
 Nihar Kulkarni  :) :)
 


 

No comments:

Post a Comment