What is CTE in sql server with example

Hello, welcome to Appsloveworld Technologies. In this session, We learn about common table expressions.

what is CTE?

Common Table Expressions are introduced in SQL server 2005. CTE is a temporary ResultSet that can be referenced within a select insert, update, or delete statement that immediately follows the CTE.

Let’s understand what we mean by this definition with an example.

So 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 used “create table” command. But to create a CTE, we use the “with” keyword.

“with” keyword, followed by the name of the CTE and then the columns that would make up your CTE. So what is the CTE is a temporary ResultSet, so obviously a ResultSet will have columns.

then you use the “AS” keyword, then another set of parentheses and then your CTE query there.

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

So 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 CTE. “As” keyoword 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 in 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 got University_Id and totalstudent. Your SELECT query has got University_Id and total student so these columns mapped to this 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 TblUniversity table.

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

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

All right. 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

The post What is CTE in sql server with example appeared first on Software Development | Programming Tutorials.