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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?