Queries expressed as a single SQL statement exploit the optimizer better than queries expressed in two or more SQL statements. SQL derived tables enable one to concisely express in a single step what might otherwise require several SQL statements and temporary tables, especially where intermediate aggregate results must be stored. For example,
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
Here aggregate results are obtained from the SQL derived tables dt_1 and dt_2, and a join is computed between the two SQL derived tables. Everything is accomplished in a single SQL statement.
For more information on SQL derived tables, see the Transact-SQL User’s Guide.
