O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required