A derived table is a SELECT statement included in the FROM clause of a query. The result set of the SELECT statement is logically treated as if it were a table. The query optimizer may also generate derived tables during query rewrites, for example in queries including the set based operations UNION, INTERSECT, or EXCEPT. The graphical plan displays the name of the derived table and the list of columns that were computed.
A derived table embodies a portion of an access plan that cannot be merged, or flattened, into the other parts of the statement's access plan without changing the query's result. A derived table is used to enforce the semantics of derived tables specified in the original statement, and may appear in a plan due to query rewrite optimizations and a variety of other reasons, particularly when the query involves one or more outer joins.
For more information on derived tables, see The FROM clause: specifying tables and FROM clause.
The following query has derived tables in its graphical plan:
SELECT EmployeeID FROM Employees UNION ALL SELECT DepartmentID FROM ( SELECT TOP 5 DepartmentID FROM Departments ORDER BY DepartmentName DESC ) MyDerivedTable;