Some of the examples from the previous section of this chapter can be solved using subqueries in the FROM clause. Subqueries work as tables in the query, and they are called derived tables. SQL Server considers these subqueries in the same way as ad hoc views, because the definition of the derived tables is merged with the outer query to produce a single query execution plan.
To use a derived table in a query, you must specify in the FROM clause a standard subquery with a table alias for the derived table (see Listing 11.15).
Correlated queries cannot be used as derived tables.
-- Using a Derived Table to ...