CTE in SQL

CTE (Common Table Expressions) provides us a temporary result set that must be used immediately with a Select,Insert,Update or Delete statements and can also be used in create view. It is defined at the start of the query and then can be executed several times in outer query.

There are two types of cte

Non-recursive CTE

A CTE that does not refer itself neither uses the property of recursion.

Syntax

WITH cte_name (col1,col2,. . . )
AS 
(
   CTE_definition
)
SELECT * FROM cte_name;

Here,

• CTE_definition is the queries.

• In place of SELECT statements,we can use insert,delete or update statements which would be used for cte.

Example 1

In this example,we are going to have a look on how cte actually works in sql by creating a cte.

Algorithm

• Step 1 − create a CTE

• Step 2 − Use AS keyword

• Step 3 − Use a select statement to extract the required data from the input table.

• Step 4 − Use select to view the data.

Input

Student

ID	Name	Course	Mail	CGPA
1	Rahul	CS	r@gmail.com	9.2
2	Suraj	AI	s@gmail.com	9.6
3	Ajeet	Mechanical	a@gmail.com	7.2
4	Manoj	Civil	m@gmail.com	8.0

Code

WITH details#cte created
AS 
(
   SELECT id,name,cgpa FROM student WHERE cgpa>8.0
   #selected the required details 
)
SELECT id,name,cgpa FROM details;#to show the result.  

Output

ID	Name	CGPA
1	Rahul	9.2
2	Suraj	9.6

Example 2

In this example, we are going to create multiple cte and join them to get the desired output. The multiple CTE definition can be defined using UNION, UNION ALL, JOIN, INTERSECT, or EXCEPT.

Algorithm

• Step 1 − Create cte

• Step 2 − Use select to extract the data from source table

• Step 3 − Create another cte

• Step 4 − Use select to extract the data from source table

• Step 5 − Use union to show results from both the cte at a single table.

Input

Student

ID	Name	Course	Mail	CGPA
1	Rahul	CS	r@gmail.com	9.2
2	Suraj	AI	s@gmail.com	9.6
3	Ajeet	Mechanical	a@gmail.com	7.2
4	Manoj	Civil	m@gmail.com	8.0

Code

WITH details#cte created
AS 
(
   SELECT id,name,cgpa FROM student WHERE cgpa>8.0
   #selected the required details 
),
table_1#another cte created
AS
(
   SELECT id,name,cgpa FROM student WHERE cgpa<7.5
   #selected the required details
)
SELECT id,name,cgpa FROM details;#to show result
UNION #used to join the cte output
SELECT id,name,cgpa FROM table_1#to show result

Output

ID	Name	CGPA
1	Rahul	9.2
2	Suraj	9.6
3	Ajeet	7.2

Recursive CTE

A recursive cte is a cte that refers to itself. It repeatedly executes until we get the complete result set.

Syntax

WITH RECURSIVE cte_name (col1,col2,. . . )
AS 
(
   CTE_definition
)
SELECT * FROM cte_name;

Here,cte_definition consists of a recursive structure using union all,distinct with clause present.

Example

In this example,we are going to see the working of recursive cte.

Algorithm

• Step 1 − Create recursive cte

• Step 2 − Select data from base table

• Step 3 − Provide condition for selecting data

• Step 4 − Use union all to combine the data

• Step 5 − Use inner join to combine records

• Step 6 − Use select to display output

Input

Student_data

ID	Name	Branch	Roll. no
1A	Monu	BAO	Null
2A	Ansh	OGI	1
3A	Rahul	IOT	2
4A	Disha	CCVT	3

Code

WITH RECURSIVE specific_data(id,name,branch,sap_id,studentlevel)
#recursive cte created
AS
(
   SELECT id,name,branch,sap_id,1#data selected from base table
   FROM student_data WHERE sap_id IS NULL
   #condition is provided
   UNION ALL#used to merge the data
   SELECT s.id,s.name,s.branch,s.sap_id,d.studentlevel+1#data selected
     FROM student_data AS s
     INNER JOIN specific_data AS d
     #used to join entities
     ON s.sap_id=d.id 
)
SELECT name,branch,studentlevel FROM specific_data
#used to display output

Output

Name	Branch	student_level
Monu	BAO	1
Ansh	OGI	2
Rahul	IOT	3
Disha	CCVT	4

Conclusion

CTE is there to provide a temporary result set that can be referred within statements like select,update,insert, or delete. CTE an be non-recursive or recursive. Non-recursive CTE does not refer to itself. In first example,we have seen the normal creation of CTE using WITH clause and in second example,we have worked on multiple CTE using union,join or intersect. The second part is recursive CTE which refers itself and gets executed repetedly. In the example,we have used WITH RECURSIVE clause to see the working of recursive CTE.