What is CTE in Sql Server With Example

In this session, We learn about common table expressions.

What is CTE?

Common Table Expressions (CTEs) were introduced in SQL Server 2005. A CTE is a temporary ResultSet that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement that immediately follows the CTE.

To create a CTE, we use the "WITH" keyword. For example, to create a stored procedure, we use the "CREATE PROCEDURE" command. Similarly, to create a table, we use the "CREATE TABLE" command. However, to create a CTE, we use the "WITH" keyword.

We start with the "WITH" keyword, followed by the name of the CTE and then the columns that would make up the CTE. Since a CTE is a temporary ResultSet, it will have columns.

Next, we use the "AS" keyword, followed by another set of parentheses, and then we define our CTE query inside these parentheses.

CTE syntext

WITH your_cte_name (Column1, Column2, ..ColumnN)
AS
( CTE_query )

Let's understand with an example. We have two tables here: TblStudent and TblUniversity tables. You can find the SQL script at the end of the post.

Now, let’s say I want you to write a query which gives me an output that you can see in the below image. I want the total number of the student by University.

Obviously, to achieve this output. I don’t have to use a CTE. I can simply achieve this by using Joins and group by. But since we are talking about CTE here, let’s see how to achieve this using CTE.

With StudentCTE(University_Id, TotalStudent)
as
(
Select University_Id, COUNT(*) as TotalStudent
from TblStudent
group by University_Id
)

Select UniversityName, TotalStudent
from TblUniversity
join StudentCTE
on TblUniversity.University_Id = StudentCTE.University_Id
order by TotalStudent

Query Explanation

I'm creating a CTE called StudentCTE. I'm using the 'WITH' keyword with StudentCTE. StudentCTE is the name of the CTE, and University_Id and TotalStudent are the columns that form the CTE. 'AS' keyword, and then your query.

If you look at the query, it's pretty straightforward. So this query will return me the total number of students by University_Id.

Select University_Id, COUNT(*) as TotalStudent
from TblStudent
group by University_Id

But if you look at the output, I want the total number of students by University name. So the SELECT query is nothing but your CTE query.

You know, this CTE has University_Id and total student. Your SELECT query has University_Id and total student so these columns are mapped to the CTE columns.

This CTE is now being used in this select query.

Select UniversityName, TotalStudent
from TblUniversity
join StudentCTE
on TblUniversity.University_Id = StudentCTE.University_Id
order by TotalStudent

This CTE is now being used in this select query. So you’re joining the StudentCTE with the TblUniversity table.

Why are we doing that? Because the StudentCTE has University_Id and totalStudent, and the TblUniversity table has University_Id and University name.

So we join the StudentCTE with the TblUniversity on the University_Id column and we retrieve the University name from TblUniversity and totalStudent from StudentCTE.

Alright. Now, the columns that you specify for the CTE are actually optional. If you don’t specify the column names for your CTE here, what is going to happen?

With StudentCTE(University_Id, TotalStudent)

The same column names are retained and then used as the columns for this CTE. So let’s execute this and we get the same output.

With StudentCTE
as
(
Select University_Id, COUNT(*) as TotalStudent
from TblStudent
group by University_Id
)

Select UniversityName, TotalStudent
from TblUniversity
join StudentCTE
on TblUniversity.University_Id = StudentCTE.University_Id
order by TotalStudent

Table SQL Script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblStudent](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [StudentName] [nvarchar](max) NOT NULL,
    [Gender] [nvarchar](50) NULL,
    [University_Id] [int] NULL,
 CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
)
GO
/****** Object:  Table [dbo].[TblUniversity]    Script Date: 09/21/2020 5:13:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblUniversity](
    [University_Id] [int] IDENTITY(1,1) NOT NULL,
    [UniversityName] [nvarchar](max) NULL,
    [Location] [nvarchar](max) NULL,
    [Country_Id] [int] NULL,
 CONSTRAINT [PK_University] PRIMARY KEY CLUSTERED 
(
    [University_Id] ASC
)
)
GO
SET IDENTITY_INSERT [dbo].[TblStudent] ON 
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (1, 'Jesiah', 'Male', 2)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (2, 'Evelyn', 'Female', 2)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (3, 'Advika', 'Female', 4)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (4, 'Bowie', 'Male', 3)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (5, 'Adweta', 'Female', 6)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (6, 'Alaric', 'Male', 5)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (7, 'Aarna', 'Female', 7)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (8, 'Rocky', 'Male', 3)
GO
SET IDENTITY_INSERT [dbo].[TblStudent] OFF
GO
SET IDENTITY_INSERT [dbo].[TblUniversity] ON 
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (1, 'Massachusetts Institute of Technology (MIT)', '77 Massachusetts Ave, Cambridge, MA 02139', 1)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (2, 'Stanford University', ' Stanford University Stanford, CA', 1)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (3, 'University of Delhi', 'Benito Juarez Marg, South Campus, South Moti Bagh, New Delhi,', 4)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (4, 'The Australian National University', 'Canberra ACT 0200, Australia', 5)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (5, 'Tsinghua University', '30 Shuangqing Rd, Haidian District, Beijing', 3)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (6, 'University of Tokyo', '7 Chome-3-1 Hongo, Bunkyo City, Tokyo 113-8654, Japan', 6)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (7, 'University of Oxford', ' Oxford OX1 2JD, United Kingdom', 2)
GO
SET IDENTITY_INSERT [dbo].[TblUniversity] OFF
GO
ALTER TABLE [dbo].[TblStudent]  WITH CHECK ADD  CONSTRAINT [FK_TblStudent_University] FOREIGN KEY([University_Id])
REFERENCES [dbo].[TblUniversity] ([University_Id])
GO
ALTER TABLE [dbo].[TblStudent] CHECK CONSTRAINT [FK_TblStudent_University]
GO
ALTER TABLE [dbo].[TblUniversity]  WITH CHECK ADD  CONSTRAINT [FK_TblUniversity_Country] FOREIGN KEY([Country_Id])
REFERENCES [dbo].[TblCountry] ([Country_Id])
GO
ALTER TABLE [dbo].[TblUniversity] CHECK CONSTRAINT [FK_TblUniversity_Country]
GO