DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use derived tables?

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

Staff NoOfOrders LastOrder Manager MgrNoOfOrders LastOrder
Hillmann, Reinout 401 9/3/2004 Word, Sheela 160 9/1/2004
Meisner, Linda 400 9/3/2004 Word, Sheela 160 9/1/2004
Hill, Annette 362 9/3/2004 Word, Sheela 160 9/1/2004
Ogisu, Fukiko 362 9/3/2004 Word, Sheela 160 9/1/2004
Hagens, Erin 361 9/3/2004 Word, Sheela 160 9/1/2004
Pellow, Frank 361 9/3/2004 Word, Sheela 160 9/1/2004
Sandberg, Mikael 361 9/3/2004 Word, Sheela 160 9/1/2004
Miller, Ben 360 9/3/2004 Word, Sheela 160 9/1/2004
Kurjan, Eric 360 9/3/2004 Word, Sheela 160 9/1/2004
Hee, Gordon 360 9/3/2004 Word, Sheela 160 9/1/2004
Rao, Arvind 164 ######## Word, Sheela 160 9/1/2004
Word, Sheela 160 9/1/2004 Kahn, Wendy NULL NULL

 

*/
------------
 
----------
-- 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)

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

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE