Joe Celko's SQL for Smarties, 3rd Edition

18.9. Using Derived Tables

A derived table can be built in the FROM clause or in a WITH [RECURSIVE] clause. The latter is new to SQL-99. Essentially, a derived table is like an in-line VIEW created by the programmer, rather than by the DBA. The WITH clause allows the query to use the same result set in several places, which was not possible with the original derived table syntax.

18.9.1. Derived Tables in the FROM clause

You have already seen the syntax and some of the uses of derived tables. Most parts of the expression are optional, but it is a very good idea to use them.

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

The tricky part is in the scoping rules for the subquery expressions. Consider this set of expressions:

SELECT ...