Common Table Expression (CTE)

Common Table Expression (CTE)

  July 04, 2017    Selva

---

 Common Table Expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement.
Complex SQL statements can be made easier to understand and maintainable in the form of CTE. CTE 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. CTE is standard ANSI SQL standard. 

CTE has supports two types of CTEs-recursive and nonrecursive 

Nonrecursive:
A nonrecursive CTE is one that does not reference itself within the CTE.

EX:

WITH  cteEmployee

  AS

  (

    SELECT Id,Name,DepartmentID

    FROM employee WHERE Name LIKE 'mark%'

  )

SELECT e.Id,e.name,d.name  

FROM cteEmployee e  

INNER JOIN Department d

ON e.DepartmentID=d.ID

Recursive:

A recursive CTE is a CTE that references itself.  In doing so, the initial CTE is repeatedly executed, returning subsets of data, until the complete result is returned.


EX:

WITH   cte
AS     (SELECT 1 AS 
        UNION ALL
        SELECT n + 1 
        FROM   cte
        WHERE  n < 10 
       )
SELECT n
FROM   cte;