Subqueries Using SQL-Derived Tables

You can use a SQL-derived table in a subquery from clause.

For example, this query finds the names of the publishers who have published business books:

select pub_name from publishers
   where "business" in
      (select type from
         (select type from titles, publishers
             where titles.pub_id = publishers.pub_id)
       dt_titles)

dt_titles is the SQL-derived table defined by the innermost select statement.

SQL-derived tables can be used in the from clause of subqueries anywhere subqueries are legal.