What is CTE and Recurssive CTE?

 CTE   (Common table expression)

            CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with semicolon.

SELECT *
FROM   (SELECT Addr.Address,
               Emp.Name,
               Emp.Age
        FROM   Address Addr
               INNER JOIN Employee Emp
                       ON Emp.EID = Addr.EID) Temp
WHERE  Temp.Age > 50
ORDER  BY Temp.NAME

By using CTE above query can be re-written as follows :

 ;WITH CTE1(Address, Name, Age)--Column names for CTE, which are optional

     AS (SELECT Addr.Address,

                Emp.Name,

                Emp.Age

         FROM   Address Addr

                INNER JOIN EMP Emp

                        ON Emp.EID = Addr.EID)

SELECT *

FROM   CTE1 --Using CTE

WHERE  CTE1.Age > 50

ORDER  BY CTE1.NAME

Recursive CTE  :

  Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression).

 

Consider following query

 ;WITH CTE1(Address, Name, Age)--Column names for CTE, which are optional

     AS (SELECT Addr.Address,

                Emp.Name,

                Emp.Age

         FROM   Address Addr

                INNER JOIN EMP Emp

                        ON Emp.EID = Addr.EID)

SELECT *

FROM   CTE1 --Using CTE

WHERE  CTE1.Age > 50

ORDER  BY CTE1.NAME

 In the above example emp_cte is a common expression table, the base record for the cte is derived by the first sql query before union all. The result of the query gives you the employeeid which don’t have managerid. Second query after union all is executed repeatedly to get results and it will continue until it returns no rows. for above e.g. result will have employeeids which have managerid (ie, employeeid of the first result).  this is obtained by joining cte result with employee table on columns employeeid of cte with managerid of table employee.

this process is recursive and will continue till there is no managerid who doesn’t have employeeid