SQL-Derived Tables and Optimization

Queries that are expressed as a single SQL statement make more efficient use of the optimizer than queries that are expressed in two or more SQL statements.

SQL-derived tables use a single step for what might otherwise require several SQL statements and temporary tables, especially where intermediate aggregate results must be stored. For example, this single SQL statement obtains aggregate results from the SQL-derived tables dt_1 and dt_2, and computes a join between the two tables:

select dt_1.* from 
   (select sum(total_sales) 
       from titles_west group by total_sales)
             dt_1(sales_sum),
   (select sum(total_sales) 
       from titles_east group by total_sales)
             dt_2(sales_sum)
where dt_1.sales_sum = dt_2.sales_sum