Common Table Expressions and Nonrecursive Queries

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.

Note – Common table expressions are also used by the APPLY operator, which allows you to invoke a table-valued function for each row returned by an outer table expression of a query.

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).

Note – The WITH keyword is ambiguous in the Transact-SQL language. To avoid ambiguity, you have to use a semicolon (;) to terminate the statement preceding the WITH statement.

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.

Example 6.78

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.

Example 6.79

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.)

Related Lessons: