SQL Common Table Expressions (CTE)

SQL CTE stands for Common Table Expression, and it was introduced in SQL Server 2005 and basically it simplify the complex SQL queries and help to write the recursive query.

SQL CTE always returns temporary result set and the scope of result is only to next SQL query and it can be referenced within a Select, INSERT, UPDATE, or DELETE statement.

A SQL CTE can be used to:

·         Use the CTE's result-set more than once in SQL query

·         Substitute for a view when the general use of a view is not required;

·         Simplify the complex joins

·         Create a recursive query

Here is an example of CTE:

We want to calculate yearly sales reports by using SQL CTE

Order Table:

OrderID	OrderAmount	OrderDate
1	$           189.00	8/21/2017 10:58
2	$             76.00	12/21/2017 10:59
3	$             76.00	6/21/2017 10:59
4	$             79.00	8/21/2016 11:00
5	$             84.00	4/21/2019 11:00

With CTE_YearlySale AS

(

 Select OrderID, OrderAmount, Year(OrderDate) [Year] from dbo.Order

)

Select [Year], SUM(OrderAmount) [Sales] from CTE_YearlySale group by [YEAR]

Output:

Year	Sales
2016	$        79.00
2017	$      341.00
2019	$        84.00

Here is an example of Recursive Query by using CTE:

WITH [CTE_Recursive] AS (

SELECT 1 AS [Number]

UNION ALL

SELECT [Number]+1

FROM [CTE_Recursive]

WHERE [Number] <= 8

)

SELECT [Number], Case When ([Number] % 2 = 1) THEN 'ODD' ELSE 'EVEN' END [Label] FROM [CTE_Recursive]

Output:

Number	Label
1	ODD
2	EVEN
3	ODD
4	EVEN
5	ODD
6	EVEN
7	ODD
8	EVEN
9	ODD

 

For more information about the SQL Common Table Expressions (CTE) visit WITH common_table_expression (Transact-SQL)