SQL Server CTE(Common Table Expression) and Recursive Queries

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

• Alternate to views, temporary tables

• 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

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

*
**
***
*****
*****

Recursive 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

Case 4: CTE Recursive query for data hierarchy (Parent Child hierarchy)

For this visit the link Recursive Data Hierarchy.

Limitations of CTE

1. Use select query of CTE in very first line immediately after CTE declaration.

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.

4. If there is a sequence of queries to be executed and if you want to use CTE query in between of them then the immediate query above CTE should end with semicolon; as shown below.

                    select 'First query'
                    select 'Second query'
                    select 'Third Query';
                    With CTE(Digit)
                    AS
                    (SELECT 100)
                    SELECT * FROM CTE

If you remove ";" at the end of the query which is just above CTE then CTE query won't be executed and throws error.

Sample for real time CTE use

For example, create 2 tables as shown below to maintain student marks and college name.

CREATE TABLE Student_Results(name varchar(50),Subject varchar(40),Marks int)
insert into Student_Results values('Andrew','subject1',60)
insert into Student_Results values('Andrew','subject2',56)
insert into Student_Results values('Andrew','subject3',44)
insert into Student_Results values('Mark','subject1',74)
insert into Student_Results values('Mark','subject2',68)
insert into Student_Results values('Mark','subject3',98)
insert into Student_Results values('Steve','subject1',90)
insert into Student_Results values('Steve','subject2',86)
insert into Student_Results values('Steve','subject3',60)


CREATE TABLE Student_College(StudentName varchar(50),CollegeName varchar(40))
INSERT INTO Student_College values('Steve','Learners College')
INSERT INTO Student_College values('Andrew','Masters College')
INSERT INTO Student_College values('Mark','Arts College')

There are 3 students with their marks in three subjects. Another table has college name for each student. Below query using with CTE displays one row for each student with average marks and college name.

With CTE_Student(Name,AverageMarks)
AS(
SELECT name, avg(Marks) FROM Student_Results GROUP BY NAME)
SELECT CTE.Name,CTE.AverageMarks,C.CollegeName FROM CTE_Student CTE 
INNER JOIN Student_College C on CTE.name=C.StudentName

Output