SQL-Derived Tables

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.

SQL-derived tables are not the same as 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

This example shows the advantages of SQL-derived tables.

If you are interested in viewing only the titles of books written in Colorado, you might create a view like this:

create view vw_colorado_titles as
     select title
     from titles, titleauthor, authors
     where titles.title_id = titleauthor.title_id
     and titleauthor.au_id = authors.au_id 
     and authors.state ="CO"

You can repeatedly use the view vw_colorado_titles, stored in memory, to display its results:

select * from vw_colorado_titles

Drop the view when it is no longer needed:

drop view vw_colorado_titles

If the query results are only needed once, you might instead use a SQL-derived table:

select title 
from (select title
      from titles, titleauthor, authors
      where titles.title_id = titleauthor.title_id
      and titleauthor.au_id = authors.au_id and
      authors.state = "CO") dt_colo_titles

The SQL-derived table created is named dt_colo_titles. The SQL-derived table persists only for the duration of the query, in contrast with a temporary table, which exists for the entire session.

In the previous example for query results that are only needed once, a view is less desirable than a SQL-derived table query because the view is more complicated, requiring both create and drop statements in addition to a select statement. The benefits of creating a view for only one query are additionally offset by the overhead of administrative tasks such as dealing with system catalogs. SQL-derived tables spontaneously create nonpersistent tables which require no administrative tasks. A SQL-derived table used multiple times performs comparably to a query using a view with a cached definition.