Explain CTE (Common Table Expression)

Common Table Expression (CTE) is mainly used for following 2 features.

1.     Alternate to views, temporary tables
2.     Recursive queries. Especially this is very useful for data hierarchy queries where parent IDs and child IDs are in same table.


Case 1: Simple CTE

With CTE_example
    AS
    (SELECT 100 Digit)
    SELECT * FROM CTE_example

We can also declare column names with CTE table declaration as shown 
below                                                                                   

With CTE(Digit)
AS
(SELECT 100)
SELECT * FROM CTE

Output:            

Digit

-----------

100
                 

 Case 2: declaring and using multiple CTEs

With CTE1(Digit_CTE1) 
AS
(SELECT 100 Digit)
 ,CTE2(Digit_CTE2)
AS
(SELECT 200 Digit)
SELECT * FROM CTE1 CROSS JOIN CTE2

Output:

Digit_CTE1       Digit_CTE2
----------------------------------
100                  200

Case 3:Recursive query using CTE:

WITH cte_alias (column_aliases)  AS  ( cte_query_definition   --initialization UNION ALL cte_query_definition2 --recursive execution )  SELECT * FROM cte_alias

without using recursive CTE query, It is not possible to to display starts(*) in ascending order 5 times using one single query as shown below,

*
**
***
*****
*****
Recusive query to display starts(*) as shown above:

With CTE_Stars
AS
(select CONVERT(VARCHAR(10),'*') Stars
UNION ALL
SELECT CONVERT(VARCHAR(10),CTE_Stars.Stars+'*') Stars FROM CTE_Stars  WHERE  LEN(Stars)<6
)
SELECT * FROM CTE_Stars

Limitations of CTE: 

 
1.Use select query of CTE in very first line immediately after CTE decleration.          
2.We can use only one select query of CTE for one CTE declaration.

3.Sub queries and outer joins won't work within CTE declaration.

CTE Example

CREATE TABLE dbo.Company
 (CompanyID int NOT NULL PRIMARY KEY,
 ParentCompanyID int NULL,
 CompanyName varchar(25) NOT NULL) 

INSERT dbo.Company (CompanyID, ParentCompanyID, CompanyName)
 VALUES (1, NULL, 'Mega-Corp')
 INSERT dbo.Company (CompanyID, ParentCompanyID, CompanyName)
 VALUES (2, 1, 'Mediamus-Corp')
 INSERT dbo.Company (CompanyID, ParentCompanyID, CompanyName)
 VALUES (3, 1, 'KindaBigus-Corp')
 INSERT dbo.Company (CompanyID, ParentCompanyID, CompanyName)
 VALUES (4, 3, 'GettinSmaller-Corp')
 INSERT dbo.Company (CompanyID, ParentCompanyID, CompanyName)
 VALUES (5, 4, 'Smallest-Corp')
 INSERT dbo.Company (CompanyID, ParentCompanyID, CompanyName)
 VALUES (6, 5, 'Puny-Corp')
 INSERT dbo.Company (CompanyID, ParentCompanyID, CompanyName)
 VALUES (7, 5, 'Small2-Corp')

WITH CompanyTree(ParentCompanyID, CompanyID, CompanyName, CompanyLevel)
 AS
 (
 SELECT ParentCompanyID,CompanyID,CompanyName,0 AS CompanyLevel
 FROM dbo.Company
 WHERE ParentCompanyID IS NULL
 UNION ALL
 SELECT c.ParentCompanyID, c.CompanyID,
 c.CompanyName, p.CompanyLevel + 1
 FROM dbo.Company c
 INNER JOIN CompanyTree p
 ON c.ParentCompanyID = p.CompanyID
 )
 SELECT ParentCompanyID, CompanyID, CompanyName, CompanyLevel
 FROM CompanyTree