Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

7.2. Common Table Expressions

SQL-99 added the common table expression, or CTE. It is also a query expression that is given a name, just like a derived table. The difference is that they appear before the SELECT statement to which they belong.

7.2.1. Nonrecursive CTEs

The syntax is fairly straightforward for the simple CTE.

WITH <cte list> <select stmt>;
<cte list> ::= <cte expr> [, <cte expr>] ..
<cte exp> ::= <cte name> [(<column name list>)]
AS (<select stmt>)

The query or other statement begins with the keyword WITH, followed by a comma-separated list of CTEs. Each CTE has a unique name within the statement, an optional list of column names, and the keyword AS (it is required) followed by the defining query. In short, the same elements we ...