Syntax of CTE:WITH CTE_NAME (Represents the column names for the defined CTE)** AS (
//
SELECT statement whose result set populates the common table expression. )
SELECT * FROM CTE_NAME
;
**Optional if there is no duplicity in column names in the result set of the CTE_query_definition
Simple CTE Example:
WITH CTE_SalesOrder
AS
(SELECT SalesOrderNo, ItemNo, Qty, UnitPrice FROMSalesOrderDetails
)SELECT * FROM CTE_SalesOrder;
This query will generate a result set from SalesOrderDetails table.
This result set can be retrieved like table or view.
Simple CTE with alias
We can use alias in common table expression (CTE). The examples are give below:
WITH CTE_SalesOrder (No, Item, Qty, Price)
AS
(
SELECT SalesOrderNo, ItemNo, Qty, UnitPrice FROMSalesOrderDetails
)
SELECT * FROM CTE_SalesOrder;
It also accepts like the following as it is in the normal select query.
WITH CTE_SalesOrder
AS
(
SELECT SalesOrderNo AS No, ItemNo AS Item, Qty AS Qty, UnitPriceAS Price FROM DBO.SalesOrderDetails
)
SELECT * FROM CTE_SalesOrder;
CTE joins with normal table
The result set of CTE can be joined with any normal table. Example of CTE joining with normal table given below.
WITH CTE_SalesOrder
AS
(
SELECT DISTINCT SalesOrderNo FROM SalesOrderDetails
)
SELECT CS.SalesOrderNo, SO.Date, SO.ClerkNo, SO.CustomerNo
FROM dbo.SalesOrder SO
INNER JOIN CTE_SalesOrder CS
ON SO.SalesOrdersNo=CS.SalesOrderNo;
Multiple resultsets in the CTE
We can join multiple CTE result set as like as tables. Here is an example of multiple result set in CTE.
WITH CTE_SalesOrder
AS
(
SELECT SalesOrderNo, ItemNo, Qty, UnitPrice FROMSalesOrderDetails
),
CTE_Item
AS
(
SELECT ItemNo, Description FROM InventoryItems
)
SELECT SO.SalesOrderNo, SO.ItemNo, IT.Description, SO.Qty,SO.UnitPrice
FROM CTE_SalesOrder SO
INNER JOIN CTE_Item It ON SO.ItemNo=IT.ItemNo;
Union statements in the CTE
Using CTE we can combine result sets with the union or union all, and can retrieve data and doing calculation on that, instead of inserting the result set into a table for the same purpose. Here is an example given below:
WITH CTE_Item
AS
(
SELECT ItemNo, Description FROM InventoryItems WHERE Description=‘TV’
UNION ALL
SELECT ItemNo, Description FROM InventoryItems WHERE Description=‘AC’
)
SELECT Item.Description, SUM(SO.UnitPrice*SO.Qty) TOTAL_PRICEFROM SalesOrderDetails SO
INNER JOIN CTE_Item Item
ON SO.ItemNo=Item.ItemNo
GROUP BY Item.Description;