Common Table Expressions are introduced in SQL Server 2005.
They are very useful when we are working with sub queries.
Sub queries are cumbersome to use when all the filtering and
matching logic has to be implemented into the sub query expressions. Sub
queries are not reusable. Data in sub query is available till it gives the
result set to the user. Sub queries are useful when we have a single task to
perform and need to use the query only once.
A work around to the limitation of sub query is to create
and populate the sub query contents into temporary table. But, it needs permissions
to create and use. We can use this temporary table as a physical table and we
can apply join and filtering expressions on this. But, it is costly with
respect to performance and it is overhead to physical disk reads.
CTE is best compared to both sub queries and temporary
tables. It is a named object which can be reused and referenced like a physical
table which gives flexibility to perform operations. It is a sub query that
exists only in memory and does not require permissions to use and won’t perform
unnecessary disk operations.
Syntax for
creating CTE:
WITH
<CTE_Name> (<Col1>,<Col2>,…)
AS
( <Select
Statement> )
CTE should be defined before it is used in the query. It
should start with the key word WITH and the output column names list with in
the parentheses and followed by SELECT statement.
Following statement selects all the male customers who are
married. (I am taking AdventureWorksDW2012 for the demo purpose) -
WITH Customer_Details ([CustomerKey],[Customer_Full_Name])
AS
(select [CustomerKey],[FirstName]+' '+
ISNULL([MiddleName],'') +' '+
ISNULL([LastName],'') as [Customer_Full_Name]
from [AdventureWorksDW2012].[dbo].[DimCustomer]
where [Gender]='M' and [MaritalStatus]='M')
Above script alone will not run by itself. We have to use it
in the subsequent query. If we try to execute the query, it throws invalid
syntax error message. After defining the CTE, we can use it as a table. Columns
that are used for joining, filtering and sorting outside the CTE, should be
defined in the output column list with in parentheses.
Following Statement will give total quantities sold by
county and promotion.
WITH Customer_Details ([CustomerKey],[Customer_Full_Name])
AS
(select [CustomerKey],[FirstName]+' '+
ISNULL([MiddleName],'') +' '+
ISNULL([LastName],'') as [Customer_Full_Name]
from [AdventureWorksDW2012].[dbo].[DimCustomer]
where [Gender]='M' and [MaritalStatus]='M')
Select
ST.[SalesTerritoryCountry] as
Country,
DP.[EnglishPromotionName] as
Promotion_Name,
sum(IST.[OrderQuantity]) as Sold_Quantity
from [AdventureWorksDW2012].[dbo].[DimSalesTerritory] as
ST
inner join [FactInternetSales] as
IST
on IST.[SalesTerritoryKey]=ST.[SalesTerritoryAlternateKey]
inner join Customer_Details as cd
on cd.[CustomerKey]=IST.[CustomerKey]
inner join [AdventureWorksDW2012].[dbo].[DimPromotion] DP
on DP.PromotionKey=IST.PromotionKey
group by [SalesTerritoryCountry],DP.[EnglishPromotionName]
order by 1
Output is –
|
Country
|
Promotion_Name
|
Sold_Quantity
|
|
Australia
|
No Discount
|
3591
|
|
Australia
|
Touring-3000 Promotion
|
1
|
|
Australia
|
Volume Discount 11 to 14
|
139
|
|
Canada
|
No Discount
|
2542
|
|
Canada
|
Touring-1000 Promotion
|
1
|
|
Canada
|
Touring-3000 Promotion
|
1
|
|
Canada
|
Volume Discount 11 to 14
|
92
|
|
France
|
No Discount
|
1308
|
|
France
|
Volume Discount 11 to 14
|
48
|
|
Germany
|
No Discount
|
1383
|
|
Germany
|
Touring-3000 Promotion
|
1
|
|
Germany
|
Volume Discount 11 to 14
|
46
|
|
United Kingdom
|
No Discount
|
1866
|
|
United Kingdom
|
Touring-1000 Promotion
|
1
|
|
United Kingdom
|
Volume Discount 11 to 14
|
75
|
|
United States
|
No Discount
|
6189
|
|
United States
|
Touring-1000 Promotion
|
4
|
|
United States
|
Touring-3000 Promotion
|
1
|
Let’s discuss how to use CTE as recursive in the next post.
Happy Learning.
