A SQL-derived table is defined by one or more tables through the evaluation of a query expression, used in the query expression in which it is defined, and exists only for the duration of the query. It is not described in system catalogs or stored on disk.
Do not confuse SQL-derived tables with abstract-plan-derived tables. A table derived from an abstract plan is used for query optimization and execution, and differs from a SQL-derived table in that it exists only as part of an abstract plan and is invisible to the end user.
A SQL-derived table is created from an expression consisting of a nested select statement, as in the following example, which returns a list of cities in the publishers table of the pubs2 database:
select city from (select city from publishers) cities
The SQL-derived table is named cities and has one column titled city. The SQL-derived table is defined by the nested select statement and persists only for the duration of the query, which returns:
city -------------------- Boston Washington Berkeley
