A Common Table Expression also called CTE in SQL, is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax in SQL server
[WITH <common_table_expression> [ ,…n ] ] <common_table_expression>::= expression_name [ ( column_name [ ,…n ] ) ] AS ( CTE_query_definition )
Diffrent Parts of CTE Syntax
- expression_name: A valid identifier for the common table expression whose name must be unique to other common table expressions defined in the same WITH <common_table_expression> clause.
- column_name: This specifies the name of the column in the common table expression where no duplicate names within a single CTE definition are allowed.
- CTE_query_definition: It is the SELECT statement where we are defining our result set. If there is more than one CTE_query_definition, the query definitions must be joined by any of these set operators: UNION ALL, UNION, EXCEPT, or INTERSECT.
Example of Common Table Expression in SQL Server
In this example, let’s consider a table “students” which contains the following data:-
WITH students_of_computer AS (SELECT * FROM students WHERE subject = ‘computer’) SELECT student_name, email, subject FROM students_of_computer;
In this example, the CTE name is students_of_computer where the subquery that defines the CTE returns the three columns student name, email, and subject. So at the end of the result, the CTE students_of_computer will return all the students who have taken the computer subject.
After defining the CTE students_of_computer, we have referenced it in the SELECT statement to get the details of students who have computers as their subject.
The result returns only those students who have taken computer as their subject.
Types of CTE in SQL
SQL has two types of CTE (Common Table Expressions) They are as follows:-
- Recursive CTE
- Non-Recursive CTE
Recursive CTE in SQL Server
CTE that references itself within that CTE is called recursive CTE(a concept based on recursion) which is very helpful while working with hierarchical data. In simple words, CTE is queries that call themselves.
WITH cte_name (column_list) AS ( — Anchor member initial_query UNION ALL — Recursive member that references cte_name recursive_query ) -- references CTE name SELECT * FROM cte_name
To understand how recursive CTE works in SQL, let us take the below example which will generate a series of the first 4 odd numbers:
WITH odd_no_cte (id, n) AS ( SELECT 1, 1 UNION ALL SELECT id+1, n+2 from odd_no_cte where id <= 4 ) SELECT * FROM odd_no_cte;
Where the output of the recursive CTE will be:-
Non-recursive CTE is just as its name sounds.CTE that doesn’t reference itself within that CTE is called non-recursive CTE and it is more simple and easy to understand as it doesn’t use recursion.
So in the end, we can conclude that CTE in SQL can be useful for the following reasons;-
- For easy readability of code
- Presence of recursive queries that calls themselves makes working on hierarchical data easy
- Code maintainability gets easier with CTE
- CTE act as a substitution for a view
What is the difference between CTE and table variables?
CTE is a named temporary result set that is used to manipulate the complex sub-queries data. You cannot create an index on CTE. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of a batch.
Is it Possible to Create an Index on CTE?
CTE in SQL or Common table expressions as named temporary result set that is used to manipulate the complex sub-queries data. You cannot create an index on CTE.