Joe Celko's SQL for Smarties, 3rd Edition

18.10. Derived Tables in the WITH Clause

A derived table built with a WITH clause is also called a “Common Table Expression” (CTE) and can be thought of as a named table within an SQL statement that exists only for the duration of that statement. There can be many CTEs in a single SQL statement. Each must have a unique name and be declared at front of a single query using the WITH clause.

The WITH clause is new to SQL-99 and the syntax is:

WITH [RECURSIVE]
<subquery expression> AS <table name> [(<column list>)]
<query expression>

As a simple example, consider a report that tells us the item(s) that had the highest sales volume, measured in dollars. First, build a query in the WITH clause to total each item by its UPC code. Then using this, find ...