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 about derived tables, see The FROM clause: Specifying tables and FROM clause.

Example

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;