A derived table closely resembles a subquery. It is a table subquery that is defined in the FROM clause of the outer query. All columns of a table expression must have names, and therefore you must assign column aliases to all columns that are the results of computations. You use the following form to define a query and a derived table:

SELECT <col_list> FROM (<inner_query>) AS <table_alias>[(<target_col_list>)]

As for physical processing, table expressions don’t get persisted anywhere , instead they get inlined. You will be able to see that fact when you look at the execution plan of the query.

Below is a very simple example of a derived table. Note that a derived table is not needed because the outer query doesn’t apply any manipulation.

SELECT * FROM
(
SELECT [custid], [city] FROM [Customers]
)
AS Cust

We can nest one within another. The following sql code produces the same results as the above code.

SELECT * FROM 
(
	SELECT * FROM
	(
	SELECT [custid], [city] FROM [Customers]
	)
	AS Cust
) AS CutomerList

Two Syntaxes

The first syntax shown below is called inline aliasing. These examples are from Itzik Ben-Gan’s book T-SQL Querying.

SELECT col1, expr1 + 1 as expr2
FROM (SELECT col1, col1 + 1 as expr1
    FROM dbo.T1) AS D

The second form is called external aliasing, as shown below.

SELECT col1, expr1 + 1 as expr2
FROM (SELECT col1, col1 + 1
    FROM dbo.T1) AS D(col1, expr1)

You can combine them if you wish.

SELECT col1, expr1 + 1 as expr2
FROM (SELECT col1, col1 + 1 AS expr1
    FROM dbo.T1) AS D(col1, expr1)