So far, I’ve covered scalar and multivalued subqueries. This section deals with table subqueries, which are known as table expressions. In this chapter, I’ll discuss derived tables and common table expressions (CTE).
For information about the two other types of table expressions—views and inline table-valued functions—please refer to Inside Microsoft SQL Server 2008: T-SQL Programming (Microsoft Press, 2009).
A derived table is a table expression—that is, a virtual result table derived from a query expression. A derived table appears in the FROM clause of a query like any other table. The scope of existence ...