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
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
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
- Use select query of CTE in very first line immediately after CTE declaration.
- We can use only one select query of CTE for one CTE declaration.
- Sub queries and outer joins won't work within CTE declaration.
- 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 CTEIf 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