Tuesday, November 14, 2017

Common Table Expression in SQL (with multiple joined CTEs) 


CTE ( common table expression ) can be taken simply as temporary dataset within the query.
It is similar to temporary table but it is not stored as an object like temporary table and is alive
until the query execution.

You can use CTE like a normal table. Complicated joins can be simplified using CTE. Below you can find examples of simple CTE and CTE with joins. These techniques can be used as per your need to get the result data.

Simple CTE


;with cte1 as (

select
       field1,
       field2,
       field3
from dbo.table1
)
select * from cte1

Multiple CTEs with joins 


;with cte1 as (

select 
field1,
field2,
field3
from dbo.table1
), cte2 as (
select 
field4,
field5
from dbo.table2
)

select 
_cte1.*,
_cte2.* 
from cte1 _cte1
inner join cte2 _cte2 on _cte2.field4 = _cte1.field1


For detailed information click the link below :
 https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx



No comments:

Post a Comment