SQL Server: Common Table Expression (CTE)

Transact-SQL successfully implements a Common Table Expression (CTE) with the following:

WITH LowPricedProducts (ProductID, ProductName, Price)
AS (SELECT p.ProductID, p.ProductName, MIN(c.Price)
FROM Product p
INNER JOIN ProductPriceHistory
ON c.ProductID = p.ProductID
GROUP BY p.ProductID, p.ProductName
HAVING MIN (c.Price) < 10)
SELECT * FROM LowPricedProducts
ORDER BY ProductName;

When defining a CTE, the WITH clause specifies the expression name that will be used in the subsequent statement. The WITH clause must contain a column list identifying the available columns, unless all columns in the expression's query have distinct names. The syntax for creating a CTE is as follows:

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

AS (CTE_query_definition)

After you create the CTE, the statement immediately following the CTE definition can reference the CTE expression by name one or more times, as if it were a table or view. Only the columns defined in the CTE expression are accessible.You can also create two CTEs in a single WITH clause by separating the expressions with a comma. Within the WITH clause, the CTE can also reference itself or another CTE that you previously created. Note that only one WITH clause is allowed, even if the query defining the CTE contains a subquery. In addition, a CTE query definition cannot contain an ORDER BY, COMPUTE, COMPUTE BY, INTO, FOR XML, or FOR BROWSE clause, or an OPTION clause that specifies query hints. CTE expressions can be used as an alternative to a view, temporary table, or subquery. Using a CTE expression makes the Transact-SQL code more readable than using a subquery, especially if the query using the CTE needs to reference the same result set multiple times. The Transact-SQL that includes two SELECT statements after the CTE definition will generate an error because the statement that uses the CTE must be the first statement after the CTE definition.
The Transact-SQL that uses an ORDER BY clause in the CTE definition will generate an error because an ORDERBY clause is not allowed for a query that defines a CTE. The Transact-SQL that omits the WITH keyword and includes an OUTPUT clause in the CTE definition will generate an error. The WITH keyword must be included to define a CTE. In addition, the OUTPUT clause is not allowed for a query that defines a CTE because the OUTPUT clause is not valid for SELECT statements. However, a DML statement that immediately follows the CTE

 

Definition and references the CTE can include an OUTPUT clause. When performing inserts, updates, deletes, and merges, you can use the OUTPUT clause to obtain and display information about the rows affected by the DML operation. The OUTPUT clause can display this information to the user, insert the data into another permanent or temporary table or table variable using an INTO clause, or pass the data to a nested DML statement for processing. Within the OUTPUT clause, you specify the column values that should be captured by using the column names with the INSERTED and DELETED prefixes.

Fig. 1 Database Containing Product History and Product Price Tables