A user interested in viewing only the titles of books written in Colorado might create a view like the following:
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”
The view vw_colorado_titles, stored in memory, can be used repeatedly to display its results:
select * from vw_colorado_titles
Once the view is no longer needed, it is dropped:
drop view vw_colorado_titles
If the query results are only needed once, the user 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 eliminate this overhead by enabling queries to spontaneously create non-persistent tables without needing to drop the tables or make insertions into the system catalog. Consequently, no administrative tasks are required. A SQL derived table used multiple times performs comparably to a query using a view with a cached definition.
