SQL Server Nonrecursive Queries with Common Table Expressions

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

This article discusses nonrecursive queries.

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

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 1 and 2 use the AdventureWorks database to show how CTEs can be used in nonrecursive queries. Example 1 uses the “convenient” features, while Example 2 solves the same problem using a nonrecursive query.

EXAMPLE 1

 

The query in Example 1 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 2 shows the use of the nonrecursive CTE, which shortens the definition of the query in Example 1.

EXAMPLE 2

 

The syntax for the WITH clause in nonrecursive queries is

 

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 2 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 2 uses the CTE called price_calc and its column year_2002 to simplify the inner query, which appears twice.)