A common table expression (CTE) is a named table expression that is supported by Transact-SQL. There are two types of queries that use CTE:
- Nonrecursive queries
- Recursive queries
The following sections describe both query types.
The nonrecursive form of a CTE can be used as an alternative to derived tables and views. Generally, a CTE is defined using the WITH statement and an additional query that refers to the name used in WITH (see Example 6.79).
Examples 6.78 and 6.79 use the AdventureWorks database to show how CTEs can be used in nonrecursive queries. Example 6.78 uses the “convenient” features, while Example 6.79 solves the same problem using a nonrecursive query.
USE AdventureWorks; SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = '2002') AND Freight > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = '2002')/2.5;Code language: PHP (php)
The query in Example 6.78 finds total dues whose values are greater than the average of all dues and whose freights are greater than 40 percent of the average of all dues. The main property of this query is that it is space-consuming, because an inner query has to be written twice. One way to shorten the syntax of the query is to create a view containing the inner query, but that is rather complicated because you would have to create the view and then drop it when you are done with the query. A better way is to write a CTE. Example 6.79 shows the use of the nonrecursive CTE, which shortens the definition of the query in Example 6.78.
USE AdventureWorks; WITH price_calc(year_2002) AS (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = '2002') SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT year_2002 FROM price_calc) AND Freight > (SELECT year_2002 FROM price_calc)/2.5;Code language: PHP (php)
The syntax for the WITH clause in nonrecursive queries is
WITH cte_name (column_list) AS ( inner_query) outer_queryCode language: PHP (php)
cte_name is the name of the CTE that specifies a resulting table. The list of columns that belong to the table expression is written in brackets. (The CTE in Example 6.79 is called price_calc and has one column, year_2002.) inner_query in the CTE syntax defines the SELECT statement, which specifies the result set of the corresponding table expression. After that, you can use the defined table expression in an outer query. (The outer query in Example 6.79 uses the CTE called price_calc and its column year_2002 to simplify the inner query, which appears twice.)