SQL Server Common Table Expressions (CTE)

What is a Common Table Expression

A Common Table Expression, also called as CTE in short form, is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. The CTE can also be used in a View.

In this article, we will see in detail about how to create and use CTEs from our SQL Server.

Syntax and Examples for Common Table Expressions

The CTE query starts with a “With” and is followed by the Expression Name. We will be using this expression name in our select query to display the result of our CTE Query and be writing our CTE query definition.

WITH expression_name [ ( column_name [,...n] ) ]

( CTE_query_definition )

To view the CTE result we use a Select query with the CTE expression name.

Select [Column1,Column2,Column3 …..] from expression_name

Select * from expression_name

Common Table Expression (CTE) Types

There are two types of CTEs: Recursive and Non-Recursive

Non-Recursive CTEs

Non-Recursive CTEs are simple where the CTE doesn’t use any recursion, or repeated processing in of a sub-routine. We will create a simple Non-Recursive CTE to display the row number from 1 to 10.

As per the CTE Syntax each CTE query will start with a “With” followed by the CTE Expression name with column list.

Here we have been using only one column as ROWNO. Next is the Query part, here we write our select query to be execute for our CTE. After creating our CTE query to run the CTE use the select statement with CTE Expression name.

;with ROWCTE(ROWNO) as

(

SELECT

ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO

FROM sys.databases

WHERE database_id <= 10

)

SELECT * FROM ROWCTE

Output: When we run the query, we can see the below output.

Recursive CTE

Recursive CTEs are use repeated procedural loops aka recursion. The recursive query call themselves until the query satisfied the condition. In a recursive CTE we should provide a where condition to terminate the recursion.:

We will see how to create a simple Recursive query to display the Row Number from 1 to 10 using a CTE.

Firstly we declare the Integer variable as “RowNo” and set the default value as 1 and we have created our first CTE query as an expression name, “ROWCTE”. In our CTE we’ll first display the default row number and next we’ll use a Union ALL to increment and display the row number 1 by one until the Row No reaches the incremented value to 10. To view the result, we will use a select query to display our CTE result.

Declare @RowNo int =1;

;with ROWCTE as

(

SELECT @RowNo as ROWNO

UNION ALL

SELECT ROWNO+1

FROM ROWCTE

WHERE RowNo < 10

)

SELECT * FROM ROWCTE

Output: When we run the query, we can see the below output.

CTE Query to display Date Range:

Let’s consider as there is a scenario to display the date from start date to end date all one by one as each row with details. In order to display the recursive data, we will be using the CTE Query.

Here we will write a CTE query to display the dates range with week number and day. For this we set the start and end date in parameter. Here in this example we have used the getdate() to set the start date as Todays date, and for end date we add 16 days from today.

CTE without Union All

Here we can see we have create a simple CTE query to display the RowNo, start date and week number. When we run this we will get only one result with RowNo as “1” ,StartDate as current date and week number along with week day.

declare @startDate datetime,

@endDate datetime;

select @startDate = getdate(),

@endDate = getdate()+16;

-- select @sDate StartDate,@eDate EndDate

;with myCTE as

(

select 1 as ROWNO,@startDate StartDate,'W - '+convert(varchar(2),

DATEPART( wk, @startDate))+' / D ('+convert(varchar(2),@startDate,106)+')' as 'WeekNumber'

)

select ROWNO,Convert(varchar(10),StartDate,105) as StartDate ,WeekNumber from myCTE ;

Output: When we run the query, we can see the below output.

CTE with Union All

In order to display the result from start date to end date one by one as recursive, we use a Union All to increment RowNo, to add the day one by one till the condition satisfied the date range, in order to stop the recursion we need set some condition. In this example, we repeat the recursion to display our records until the date is less than or equal to the end date.

declare @startDate datetime,

@endDate datetime;

select @startDate = getdate(),

@endDate = getdate()+16;

-- select @sDate StartDate,@eDate EndDate

;with myCTE as

(

select 1 as ROWNO,@startDate StartDate,'W - '+convert(varchar(2),

DATEPART( wk, @startDate))+' / D ('+convert(varchar(2),@startDate,106)+')' as 'WeekNumber'

union all

select ROWNO+1 ,dateadd(DAY, 1, StartDate) ,

'W - '+convert(varchar(2),DATEPART( wk, StartDate))+' / D ('+convert(varchar(2),

dateadd(DAY, 1, StartDate),106)+')' as 'WeekNumber'

FROM myCTE

WHERE dateadd(DAY, 1, StartDate)<= @endDate

)

select ROWNO,Convert(varchar(10),StartDate,105) as StartDate ,WeekNumber from myCTE

Output: When we run the query, we can see the below output.

Multiple CTE

In some scenarios, we need to create more than one CTE query and join them to display our result. In this case, we can use the Multiple CTEs. We can create a multiple CTE query and combine them into one single query by using the comma. Multiple CTE need to be separate by “,” comma fallowed by CTE name.

We will be using above same date range example to use more than one CTE query, here we can see as we have created two CTE query as CTE1 and CTE 2 to display date range result for both CTE1 and for CTE2.

Example :

Declare @startDate datetime,@endDate datetime;

Declare @startDate1 datetime,@endDate1 datetime;

Set @startDate = '2017-02-10'; Set @endDate = '2017-02-15';

Set @startDate1 = '2017-02-16'; Set @endDate1 = '2017-02-28';

WITH CTE1

AS (

SELECT 'CTE1' CTEType ,@startDate StartDate,'W'+convert(varchar(2),DATEPART( wk, @startDate))+'('+convert(varchar(2),@startDate,106)+')' as 'WeekNumber'

UNION ALL

SELECT CTEType, dateadd(DAY, 1, StartDate) ,'W'+convert(varchar(2),DATEPART( wk, StartDate))+'('+convert(varchar(2),dateadd(DAY, 1, StartDate),106)+')' as 'WeekNumber'

FROM CTE1

WHERE dateadd(DAY, 1, StartDate)<= @endDate

CTE2

AS (

SELECT 'CTE2' CTEType, @startDate StartDate,'W'+convert(varchar(2),DATEPART( wk, @startDate1))+'('+convert(varchar(2),@startDate1,106)+')' as 'WeekNumber'

UNION ALL

SELECT 'CTE2' valuetype, dateadd(DAY, 1, StartDate) ,'W'+convert(varchar(2),DATEPART( wk, StartDate))+'('+convert(varchar(2),dateadd(DAY, 1, StartDate),106)+')' as 'WeekNumber'

FROM CTE2

WHERE dateadd(DAY, 1, StartDate)<= @endDate1

)

SELECT CTEType, Convert(varchar(10),StartDate,105) as StartDate ,WeekNumber FROM CTE1

UNION ALL

SELECT CTEType, Convert(varchar(10),StartDate,105) as StartDate ,WeekNumber FROM CTE2

Output: When we run the query, we can see the below output.

Using CTE query for SQL Table

Now let’s see on, how to use CTE query for our SQL server table data.

Create Database: First, we create a database for creating our table

USE MASTER

-- 1) Check for the Database Exists .If the database is exist then drop and create new DB

IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'CTEDB' )

DROP DATABASE CTEDB

CREATE DATABASE CTEDB

USE CTEDB

Create Table: Now we create a sample Item Table on the created Database.

IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'ItemDetails' )

DROP TABLE ItemDetails

CREATE TABLE ItemDetails

(

Item_ID int identity(1,1),

Item_Name VARCHAR(100) NOT NULL,

Item_Price int NOT NULL,

Date VARCHAR(100) NOT NULL ,

CONSTRAINT [PK_ItemDetails] PRIMARY KEY CLUSTERED

(

[Item_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Insert Sample Data: We will insert few sample records for using in our CTE Query.

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Access Point',950,'2017-02-10')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('CD',350,'2017-02-13')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Desktop Computer',1400,'2017-02-16')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('DVD',1390,'2017-03-05')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('DVD Player',450,'2017-05-07')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Floppy',1250,'2017-05-07')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('HDD',950,'2017-07-10')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('MobilePhone',1150,'2017-07-10')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Mouse',399,'2017-08-12')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('MP3 Player ',897,'2017-08-14')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Notebook',750,'2017-08-16')

Insert into ItemDetails(Item_Name,Item_Price, Date) values('Printer',675,'2017-07-18')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('RAM',1950,'2017-09-23')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('Smart Phone',679,'2017-09-10')

Insert into ItemDetails(Item_Name,Item_Price,Date) values('USB',950,'2017-02-26')

select * from ItemDetails

CTE Example:

Now we will create a simple temporary result using CTE Query. Here in this CTE Query we have given the expression name as “itemCTE” and we have added the list of Columns which we use in the CTE query. In the CTE query we display all item details with the year.

;WITH itemCTE (Item_ID, Item_Name, Item_Price,SalesYear)

(

SELECT Item_ID, Item_Name, Item_Price ,YEAR(Date) SalesYear

FROM ItemDetails

)

Select * from itemCTE

Output: When we run the query, we can see the below output.

CTE using Union ALL

Let’s consider there is a below two scenarios to display the result.

The first scenario is to display each Item Price of current Year.
The second scenario is to increment 10% to each Item Price for next year.

For this we use the above CTE Query. In this query, we add the UNION ALL and in UNION ALL Query we do calculation to add 10% to each item Price and show in next row with adding one year.

;WITH itemCTE (Item_ID, Item_Name, Item_Price,MarketRate,SalesYear)

(

SELECT Item_ID, Item_Name, Item_Price ,'Present Price' as MarketRate,YEAR(Date) as SalesYear

FROM ItemDetails

UNION ALL

SELECT Item_ID as Item_ID, Item_Name,

(Item_Price + (Item_Price *10 )/100) as Item_Price,

'Future Price' as MarketRate, YEAR(dateadd(YEAR, 1, Date)) as SalesYear

FROM ItemDetails

)

SELECT * from itemCTE Order by Item_Name,SalesYear

Output: When we run the query, we can see the below output.

Common Table Expressions (CTE) for Insert

Now we will see how to insert the CTE result to another table. For this let’s consider our above Item Table. We insert the Item details result of above CTE query to Item History table. For this first we create an Item History table.

Create Item History Table: In this history table, we add the same columns as item table along with MarketRate column as present or future Item price. Here is the query to create an ItemHistory table.

CREATE TABLE ItemHistory

(

ID int identity(1,1),

oldITEMID int,

Item_Name VARCHAR(100) NOT NULL,

Item_Price int NOT NULL,

MarketRate VARCHAR(100) NOT NULL,

Date VARCHAR(100) NOT NULL )

CTE Insert Example:

Here we use above same CTE query Insert the result in to the Item History table. From this query we insert both item details of present year Item price along with the next year Item prices added as 10% more.

;WITH itemCTE (Item_ID, Item_Name, Item_Price,MarketRate,Date)

(

SELECT Item_ID, Item_Name, Item_Price ,'Present Price' as MarketRate,Date

FROM ItemDetails

UNION ALL

SELECT Item_ID as Item_ID, Item_Name,(Item_Price + (Item_Price *10 )/100) as Item_Price,

'Future Price' as MarketRate, dateadd(YEAR, 1, Date) as Date

FROM ItemDetails

)

-- Define the outer query referencing the CTE name.

Insert into ItemHistory(oldITEMID ,Item_Name,Item_Price,MarketRate,Date)

SELECT Item_ID, Item_Name, Item_Price,MarketRate,year(Date) from itemCTE Order by Item_Name,Date

Output: When we run the query, we can see the below output as 30 records has been inserted into our Item History table.

Select Query:

To view the item history result we select and display all the details.

select * from ItemHistory

Output: When we run the query, we can see the below output from item history table.

Create View with CTE Example:

Now we see how to use the above CTE query can be used in a view. Here we create a view and we add the CTE result inside the view. When we select the view as a result, we can see the CTE output will be displayed.

Example Query:

CREATE VIEW CTEVIEW

WITH itemCTE1 AS

(

SELECT Item_ID, Item_Name, Item_Price ,'Present Price' as MarketRate,Date as IDate

FROM ItemDetails

UNION ALL

SELECT Item_ID as Item_ID, Item_Name,(Item_Price + (Item_Price *10 )/100) as Item_Price,

'Future Price' as MarketRate, dateadd(YEAR, 1, Date) as IDate

FROM ItemDetails

)

SELECT Item_ID, Item_Name, Item_Price,MarketRate,year(IDate) as IDate from itemCTE1

-- T-SQL test view

SELECT * FROM CTEVIEW Order by Item_Name,IDate

Output: When we run the query, we can see the below output as result from the View.

How to write a clean CTE Query:

Here are some basic guidelines that need to be followed to write a good CTE Query.

A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.
Multiple CTE query definitions can be defined in a non recursive CTE.
A CTE can reference itself and previously defined CTEs in the same WITH clause
We can use only one With Clause in a CTE
ORDER BY, INTO, COMPUTE or COMPUTE BY, OPTION, FOR XML cannot be used in non-recursive CTE query definition
SELECT DISTINCT, GROUP BY, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed) subqueries cannot be used in a recursive CTE query definition.

Conclusion

CTEs can be used to create a recursive query and can be used to reference itself multiple times. CTEs can be used instead of views and finally a CTE is easy and simple for readability and code maintainability.

Author
Recent Posts

Syed Shanu

Syed Shanu is a Microsoft MVP, two-time CsharpCorner MVP and two-time Code project MVP, Author, Blogger, Speaker and always happy to share what he knows to others.

He’s basically from Madurai, Tamil Nadu, India. He’s working as Technical Lead in South Korea with more than 10 years of experience on Microsoft technologies.

Shanu is active in the community and always happy to share topics related to ASP.NET, MVC, ASP.NET Core, Web API, SQL Server, UWP, Azure, C#, AngularJs, Angular2. He has written more than 70 articles with various technology’s on ASP.NET, SQL Server, C#, AngularJs, Angular2, ASP.NET Core, Unity 3D, Windows Universal App and Samsung Gear App development. He’s several times TechNet Guru Gold Winner and you can found all his contribution in MSDN and also in TechNet Wiki https://social.technet.microsoft.com/profile/syedshanu/

He is also an Author of the eBook "SQL Queries For Beginners" you can download the eBook from here: http://www.c-sharpcorner.com/ebooks/sql-queries-for-beginners.

View all posts by Syed Shanu

What is a Common Table Expression

Syntax and Examples for Common Table Expressions

Common Table Expression (CTE) Types

Multiple CTE

Using CTE query for SQL Table

Common Table Expressions (CTE) for Insert

Create View with CTE Example:

Conclusion

Related posts: