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. 

CTE syntax in SQL server

[WITH <common_table_expression> [ ,…n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ ,…n ] ) ]
AS
( CTE_query_definition )

Reference:- https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

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 CTE in SQL Server

In this example, let’s consider a table “students” which contains the following data:-

IdNameEmailSubject
1Riya[email protected]Computer
2Amisha[email protected]physics
3Priya[email protected]history
4Kiara[email protected]computer
5Mini[email protected]English
6Raju[email protected]computer
7Akash[email protected]physics
8Rajesh[email protected]computer

Query:

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.

Output:

IdNameEmailSubject
1Riya[email protected]Computer
4Kiara[email protected]computer
6Raju[email protected]computer
8Rajesh[email protected]computer

The result returns only those students’ information 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:-

  1. Recursive CTE
  2. 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.

Syntax:

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 &nbsp; cte_name

Example of Recursive CTE in SQL

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:-

id no
11
23
35
47

Non-recursive CTE

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.

Conclusion

So in the end, we can conclude that the 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.

83 / 100

1 Comment

Comments are closed