Using Common Table Expressions
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
A CTE can be used to:
Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
Reference the resulting table multiple times in the same statement.
Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.
CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.
A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined, it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.
The basic syntax structure for a CTE is:
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.
The statement to run the CTE is:
SELECT <column_list>
FROM expression_name;
Example
The following example shows the components of the CTE structure: expression name, column list, and query. The CTE expression Sales_CTE has three columns (SalesPersonID, SalesOrderID, and OrderDate) and is defined as the total number of sales orders per year for each salesperson.
USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO
Here is a partial result set:
SalesPersonID TotalSales SalesYear
------------- ----------- -----------
274 4 2001
274 20 2002
274 14 2003
274 10 2004
275 56 2001
275 139 2002
275 169 2003
- 12/6/2011
- PIEBALDconsult
- 12/23/2011
- manub22
- 10/4/2011
- manub22
http://sqlwithmanoj.wordpress.com/2011/05/23/cte-recursion-sequence-dates-factorial-fibonacci-series/
- 9/13/2011
- manub22
;with
cte_Orders
AS
(
SELECT
ID
,Description
,OrderDate
FROM
Orders
WHERE
OrderDate BETWEEN '2011-03-01' AND '2011-03-31'
)
,cte_OrderParts
AS
(
SELECT
SUM(UnitPrice * Quantity) AS Amount
,Order_ID
FROM
OrderParts
INNER JOIN cte_Orders ON Order_ID = cte_Orders.ID
GROUP BY
Order_ID
)
,cte_OrderLabor
AS
(
SELECT
SUM(UnitPrice * Quantity) AS Amount
,Order_ID
FROM
OrderLabor
INNER JOIN cte_Orders ON Order_ID = cte_Orders.ID
GROUP BY
Order_ID
)
SELECT
ID
,Description
,OrderDate
,COALESCE(cte_OrderParts.Amount, 0.00) AS PartsAmount
,COALESCE(cte_OrderLabor.Amount, 0.00) AS LaborAmount
FROM
cte_Orders
LEFT JOIN cte_OrderParts ON ID = cte_OrderParts.Order_ID
LEFT JOIN cte_OrderLabor ON ID = cte_OrderLabor.Order_ID
This is an example where the cte_Orders was used multiple times. The reason I did it this was is that it will reduce the parts and labor information pulled to just those relevant to the orders we want making it more efficient because it doesn't have to do unecessary calculations.
- 6/15/2011
- Charles Peet
- 6/17/2011
- Charles Peet
*
* SELECT * FROM MyCTE ct ORDER BY ct.CustomerName
* SELECT * FROM MyCTE ct ORDER BY ct.Town -- Except you cannot reference a CTE more than once!
*
* See http://msdn.microsoft.com/en-us/library/ms190766.aspx
Because the two select statements are actually separate queries. When they say CTEs can be referenced multiple times in the same query, it means only the same query. In your example the first SELECT statement ordering by customername is one query and the second is a completely separate query (i.e. not the same query).
- 10/30/2010
- John P Thompson
- 5/27/2011
- LegacyBass
- 1/26/2011
- kettch
with ct (CityID,StateID,CityName) as
(
Select CityID,StateID,CityName
from City
)
,
st (StateID,StateName, regionID) as
(
Select StateID,StateName, regionid from state
)
,
rt (RegionID,RegionName) as
(
Select rr.RegionID,rr.RegionName from region rr
join st on st.RegionID = rr.RegionID
)
Select ct.CityName, st.StateName, rt.RegionName
From CT
join ST on ct.stateID = st.stateID
join rt on rt.RegionID = rt.RegionID
in above query the ST cte is being used twice.
- 1/21/2011
- Pankaj Rastogi
