Execute the following Microsoft T-SQL example scripts in SQL Server Management Studio Query Editor to demonstrate the use of deribed tables in complex queries.

Starting with SQL Server 2005, derived table is replaced by Common Table Expression which allows structured programming in T-SQL. It is fairly easy to rewrite a query with derived table to applying CTE as shown in the second example.
------------
-- Microsoft SQL Server T-SQL derived tables
------------
-- In the following query, OE and OM are derived tables
-- SQL select from select - subquery - subselect - group by derived table
-- MSSQL inner join - left outer join - left join
USE AdventureWorks;
 
SELECT Staff=C.LastName + ', ' + C.FirstName,
       NoOfOrders=OE.NumOrders,
       LastOrder = convert(CHAR(10),OE.MaxDate,111),
       Manager=C2.LastName + ', ' + C2.FirstName,
       MgrNoOfOrders=OM.NumOrders,
       LastOrder = convert(CHAR(10),OM.MaxDate,111)
FROM   Person.Contact AS C
       INNER JOIN HumanResources.Employee AS E
         ON C.ContactID = E.ContactID
       INNER JOIN (SELECT   EmployeeID,

                      COUNT(* ),

                      MAX(OrderDate)

             FROM     Purchasing.PurchaseOrderHeader

             GROUP BY EmployeeID) AS OE(EmployeeID,NumOrders,MaxDate)
         ON E.EmployeeID = OE.EmployeeID
       LEFT OUTER JOIN (SELECT   EmployeeID,

                                 COUNT(* ),

                                 MAX(OrderDate)

                        FROM     Purchasing.PurchaseOrderHeader

                        GROUP BY EmployeeID) AS OM(EmployeeID,NumOrders,MaxDate)
         ON E.ManagerID = OM.EmployeeID
       INNER JOIN HumanResources.Employee E2
         ON E2.EmployeeID = E.ManagerID
       INNER JOIN Person.Contact C2
         ON C2.ContactID = E2.ContactID
ORDER BY NoOfOrders DESC;

GO

/* Results

*/
------------
 
----------
-- SQL Common Table Expression (CTE) is an enhanced derived table
----------
USE AdventureWorks;
GO
-- T-SQL employee list with address information
-- MSSQL derived table: acs
SELECT   LTRIM(c.LastName) + ', ' + RTRIM(c.FirstName) AS EmployeeName,
         acs.AddressLine1                              AS Address,
         acs.City                                      AS City,
         acs.StateProvinceCode                         AS State
FROM     Person.Contact AS c
         INNER JOIN HumanResources.Employee AS e
           ON c.ContactID = e.ContactID
         INNER JOIN HumanResources.EmployeeAddress AS ea
           ON e.EmployeeID = ea.EmployeeID
         INNER JOIN (SELECT AddressID,
                            AddressLine1,
                            City,
                            StateProvinceCode
                     FROM   Person.Address a
                            INNER JOIN Person.StateProvince sp
                              ON a.StateProvinceID = sp.StateProvinceID) AS acs
           ON ea.AddressID = acs.AddressID
ORDER BY EmployeeName;
GO
/* Partial results
 
EmployeeName            Address                 City        State
Abbas, Syed             7484 Roundtree Drive    Bothell     WA
Abercrombie, Kim        9752 Jeanne Circle      Carnation   WA
Abolrous, Hazem         5050 Mt. Wilson Way     Kenmore     WA
Ackerman, Pilar         5407 Cougar Way         Seattle     WA
Adams, Jay              896 Southdale           Monroe      WA
Ajenstat, François      1144 Paradise Ct.       Issaquah    WA
Alberts, Amy            5009 Orange Street      Renton      WA
Alderson, Greg          8684 Military East      Bellevue    WA
*/
 
-- Equivalent query using Common Table Expression (CTE) instead of derived table
; WITH cteAddressCityState
     AS (SELECT AddressID,
                AddressLine1,
                City,
                StateProvinceCode
         FROM   Person.Address a
                INNER JOIN Person.StateProvince sp
                  ON a.StateProvinceID = sp.StateProvinceID)
SELECT   LTRIM(c.LastName) + ', ' + RTRIM(c.FirstName) AS EmployeeName,
         acs.AddressLine1                              AS Address,
         acs.City                                      AS City,
         acs.StateProvinceCode                         AS State
FROM     Person.Contact AS c
         INNER JOIN HumanResources.Employee AS e
           ON c.ContactID = e.ContactID
         INNER JOIN HumanResources.EmployeeAddress AS ea
           ON e.EmployeeID = ea.EmployeeID
         INNER JOIN cteAddressCityState AS acs
           ON ea.AddressID = acs.AddressID
ORDER BY EmployeeName;
 
GO

-- (290 row(s) affected)

------------