O'Reilly logo

Microsoft® SQL Server 2012 Unleashed by Alex T. Silverstein, Chris Gallelli, Paul T. Bertucci, Ray Rankins

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

Common Table Expressions

A common table expression (CTE) is an ANSI SQL-99 expression that produces a table that is referred to by name within the context of a single query. The general syntax for a CTE is as follows:

[;]WITH expression_name [ ( column_name [ ,...n ] ) ]    AS ( CTE_query_definition )

The WITH clause, in effect, defines a table and its columns. Note that the syntax of the WITH clause is similar to that of a view. You can think of a CTE as a temporary view that lasts only for the life of the query that defines the CTE. Listing 45.20 shows an example of a simple CTE. This CTE is used to return the average and maximum order subtotals for each customer. The CTE is then joined to the SalesOrderHeader ...

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