RSS

Recursive using Common Table Expressions (CTE)

29 Jul

Introduction

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to

  • Create a recursive query (Current Example)
  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  • Reference the resulting table multiple times in the same statement.

    Syntax of CTE

    with CTE_Name(Col1,Col2,Col3..)
    as (
    –Anchor Query
    Union All
    –Recursive member is defined referencing cte_name
    )
    Select * From CTE_Name

    Example

    use AdventureWorks

    go

     

    with CTE_EmployeeLevel(EmployeeId,FullName,ManagerId,ManagerName,EmpLevel)

    as

    (

    select

    EmployeeID,

    (Select FirstName +‘ ‘+LastName From Person.Contact where Person.Contact.ContactID= EmployeeID)FullName,

    ManagerID,

    (Select FirstName +‘ ‘+LastName From Person.Contact where Person.Contact.ContactID= ManagerID)ManagerName,

    1

    From HumanResources.Employee

    where ManagerId Is Null

     

    union all

     

    Select

    tbEmp.EmployeeID,

    (Select FirstName +‘ ‘+LastName From Person.Contact where Person.Contact.ContactID= tbEmp.EmployeeID)FullName,

    tbEmp.ManagerID,

    (Select FirstName +‘ ‘+LastName From Person.Contact where Person.Contact.ContactID= tbEmp.ManagerID)ManagerName,

    EmpLevel+1

    From HumanResources.Employee tbEmp inner join CTE_EmployeeLevel CTEEmp

    on tbEmp.ManagerID=CTEEmp.EmployeeId

    )

     

    Select * From CTE_EmployeeLevel

    Exam
    ple Code Walkthrough

    when Anchor query run the result set will be

    EmployeeId EmployeeName ManagerId ManagerName EmpLevel
    Stephanie Bourne 109 NULL NULL 1

    then We will take Employee Id from above result set then join it with employee id from Employee table

    Then the result will be

    EmployeeId EmployeeName ManagerId ManagerName EmpLevel
    6 Frances Adams 109 Stephanie Bourne 2

    we will repeat step before again Then We will take Employee Id from above result set then join it with employee id from Employee table

    Then the result will be

    EmployeeId EmployeeName ManagerId ManagerName EmpLevel
    2 Catherine Abel 6 Frances Adams 3
    46 Stephen Ayers 6 Frances Adams 3
    106 Corinna Bolender 6 Frances Adams 3
    119 Christopher Bright 6 Frances Adams 3

    we will repeat step before again Then we will take Employee Id from above result set then join employee id from Employee if no result

    CTE start join all result set to be

    EmployeeId EmployeeName ManagerId ManagerName EmpLevel
    Stephanie Bourne 109 NULL NULL 1
    6 Frances Adams 109 Stephanie Bourne 2
    2 Catherine Abel 6 Frances Adams 3
    46 Stephen Ayers 6 Frances Adams 3
    106 Corinna Bolender 6 Frances Adams 3
    119 Christopher Bright 6 Frances Adams 3
  • Advertisements
     

    Tags: , , ,

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: