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
- does it makes sense to do SP for simple queries like select * from users
- SQL Server String or binary data would be truncated
- Apply like function on an array is SQL Server
- SQL Server Insert Null error in spite of providing not null values
- C# simple code to write an INSERT query is giving an exception
- Could not allocate space for object dbo.table
- Join two tables in such way that I can get all items for all dates
- Display the row by row wise
- How to avoid computed-column duplicate computation in an SQL Query
- How do you get leading wildcard full-text searches to work in SQL Server?
- Sql - how to calculate 2 separate sub queries?
- Query to return data where Id Column is unique but display number is the same?
- Get Instance Name from Analysis Services like @@ServerName
- Import Wizard Error
- Protecting SQL Insert Of CSV File From SQL Injection
- Reading uncommitted data inside transaction (MS SQL Server 2008)
- Can't Jackson serialize Hibernate Entity
- Storing, uploading and downloading files in ASP.NET and SQL Server database
- Paramaterized SQL C# with DateTimes
- Why is SQL Server Full Text Search indexing SCR or SUR acronym followed by a number, together?
- sp_executesql inside Try & Catch
- RODBC::sqlsave() truncates col in DF when saving to SQL Server to varchar(255)
- How are parameters passed from SQL Server to a CLR based stored procedure?
- Parent-Child without FK inserting in wrong order
- Efficient way to store a dynamic questionnaire?
- Can I get duplicate results (from one table) in an INTERSECT operation between two tables?
- T-SQL constraint to check if value is either 0 OR unique
- SQLCMD Passing in Variables
- SQL Server query xml column
- SQL Server Agent Job History - Delete for only one day
- SQL Server OpenQuery to Exec command
- SQL Shifting 2 columns a row down with rollover
- Find the exact error record
- SQL Server: Add a column with calculated Field
- What namespace holds information on the SQL Server TinyInt datatype?
- Closed recordset returned (instead of error) when executing command after connection loss
- SQL Server 2008 R2 to MySQL Migration
- SQL Server function parameters
- SQL Powershell Module is not installed on your agent machine. Please follow steps given below to execute this task
- How to remove Case Sensitive check in SQL Server 2008?
- Using a stored procedure to update columns in C#
- Returning values in two rows that aren't equal
- Junction Table, Insert and Select
- after update trigger to deal with multiple rows
- Child Records getting skipped because of pagination in SQL
- How to design audit dimension in the data mart using SQL Server 2012
- SQL Server Docker Compose SQLCMD does not execute
- Concurrent reads using SQLFileStream?
- Full Text Search in SQL Server 2008 shows wrong display_item for Thai language
- Get aggregate value from inner query
- SQL select between two dates and time
- How can I add external value to DataSource by Sql command?
- flat data Hierarchical order
- Have Sql SMO Delete Database Backup File
- MSsql update column from joint table
- Convert the contents of a SQL Server Column into a JSON Format
- sql azure reporting solutions
- Get current month details in sql. Like, how many weeks in month?, Count of weekdays with there name?
- How to keep an audit/history of changes to the table
- need count of Range in sql query
- Get the max length of each column dynamically SQL
- Truly empty element with sql server for xml-directive
- T-SQL select and update with lock - transaction or table hint
- SQL: need to query over 5900 tables for a specific value, but encountering memory allocation error
- TSql Case within Case
- INSERT deleted values into a table before DELETE with a DELETE TRIGGER
- How to get the goal name or goal ID from Google Analytics in SSIS?
- Generic query with Entity Framework ORM
- SQL: Column Validation against GETDATE()
- Update a specific column with a specific value
- Eliminating NULLs when using CASE in SQL Server SELECT statement
- web application form insert data into database
- How to send data between electron ipcMain and angular ipcRenderer asynchronously with observables
- Update table data by keeping only newest modification date and time of records w/ no duplicates
- Executing an SQLCommand without specifying a Transaction
- TSQL -Write FIZZBUZZ without using loop
- TSQL: Find a continuous number in a string
- Arithmetic overflow error converting expression to data type numeric
- Is it possible to create a deadlock with read-only access?
- Updating multiple columns using Microsoft SQL Server
- Relation between column in one table
- How to Export binary data in SqlServer to file using DTS
- Sql Server: Get OHLC in one query with n recodrs
- Arranging Rows with Similar Column Data Together After Using ORDER BY
- How can I keep databases of two web applications in sync?
- Changes to procedure are not saved when running alter procedure
- Find Tuesday of the week based on the date in SQL
- SQL narrowing down the results of a query with lag and lead
- Accessing various databases as one in SQL Server
- Parsing out multiple numbers numbers from a text column using SQL
- Convert between text and varchar(MAX) in SQL Server
- T-SQL Query for extracting multiple column conditions
- Full text index search for complex query in Entity framework
- Sql server tuning advisor report
- Dropdownlist Selected Item is not showing correct item
- Execute query as per condition using case statement in joining
- Unable to instantiate driver SQLServerDriver in Wildfly 10
- SQL join without losing rows
- SQL script to partition data on a column and return the max value
- Referencing column alias in JOIN ON clause
- Check if data follows planned order
- SQL Server - Get comma separated column names which have values
- Error converting data type varchar to bigint. Inline sql statements
- Create two or more triggers in a row by executing a query with SQL Server
- SQL case expression to create a range
- MSSQL bigint Unix Timestamp to Datetime with milliseconds
- SQL Server job scheduling and PHP
- SQL insert into - from different server and credentials
- Running Stored Procedure with parameters resulting from query
- SQL Server 2008 - adding a column to a replicated table fails
- Transpose SQL columns to rows
- How can I manually prevent a file from automatically loading in the designer when I open a solution?
- SQL INSERT using tables where the columns have different data types and getting error converting
- INSERT INTO using SELECT and increment value in a column
- SQL Server: Arithmetic overflow error converting expression to data type bigint
- SQL Server user-defined aggregate returns an error if aggregating 0 rows
- Finding 'break' records
- SQL - Getting MAX AVG
- What are people using to unit test their stored procedures ,etc
- Customized Primary Key on SQL Server 2008 R2
- Prevent record count in a part of SQL statement for SQL Server
- SQL Server - select pairs of available stock options
- VBScript - Retrieving a Scalar Value From a Stored Procedure on SQL Server 2008
- Store new permanent table in schema using compute
- T-SQL - In Single Stored procedure want to pass value(result) of one query to another query as input
- sql server 2008 r2: case statement in where clause for current fiscal year
- I am trying to streamline or speed up the following query:
- color first five rows of a table in SSRS
- Matching column values as prefixes
- Select the lowest date from a range and exclude another range
- T-SQL progressive numbering partitions
- T-SQL: calculate age then add character to result
- Perform date range query from a single column control table (SQL Server)
- SQL server 2008: How to get the start date and end date of my data?
- Speed up Python executemany with "Insert or Ignore"-Statement
- Using the Web.Config to set up my SQL database connection string?
- Handling very large strings between SQL Server and .NET code +LINQ
- SQL Server date diff for rows with certain conditions
- SQL query stuck - comparison on different lines
- SSIS processing large amount of flat files is painfully slow
- Building string from table data using select query/pivot
- Computer name is needed for "Data Source" parameter of dynamic connection string
- Stored procedure runs much slower than query
- Windows Invariant Culture Puzzle
- Sql Server subscription agent in running state
- String was not recognized as a valid DateTime [Remove Time from DateTime]
- Issue Creating New MSSQL Record in Node/Angular App
- SQL Trigger to copy data to table with more number of columns
- Reaching child records in a SQL Server table
- How can I call Include in Entity Framework after filter query