T-SQL – Derived Tables Demonstrated and Explained

T-SQL is a very powerful language when dealing with sets and in this post, I am going to be demonstrating how to use derived tables.

Derived tables are not the same as a temporary or physical tables where the fields and data types are declared. Nor are they subqueries. Instead, as the name implies, the table is derived from an existing table. I liken them to views but a view is an object that is created and is re-usable. A derived table is only available at execution time and not a re-usable object within the query. In other words, you cannot reference it multiple times like you can with common table expressions (CTE’s).

Basic syntax of the derived table

SELECT Field1, Field2
FROM Table) Table1

So lets put the syntax to something more real:

USE AdventureWorks2012
SELECT TOP 10 FirstName, LastName
(SELECT * FROM Person.Person) AS PersonDerivedTable
WHERE LastName = 'Smith';
FirstName       LastName
--------------- ---------------
Abigail         Smith
Adriana         Smith
Alexander       Smith
Alexandra       Smith
Alexis          Smith
Allen           Smith
Alyssa          Smith
Andre           Smith
Andrew          Smith
Arthur          Smith

(10 row(s) affected)

You might be thinking that you can achieve the same thing without the derived table and you would be correct. However where derived tables start to become really useful are when you want to start using aggregated statements.

Derived tables used with aggregated queries

For example, the following query aggregates total sales orders by year as one query and combines the results of the total sales orders per sales rep, per year of the second query. The purpose is to get the percentage of sales orders by sales rep for each year.

USE AdventureWorks2012

SELECT YearTotalsBySalesPerson.*
, YearTotals.TotalOrdersForYear
, CONVERT(DECIMAL(10,2), 100 * CONVERT(DECIMAL(10,2), YearTotalsBySalesPerson.SalesPersonTotalOrders)
/ CONVERT(DECIMAL(10,2), YearTotals.TotalOrdersForYear)) AS PercentageOfSalesForYear
--total sales orders per year
(SELECT YEAR(OrderDate) AS [Year]
, COUNT(SalesOrderID) AS TotalOrdersForYear
FROM Sales.SalesOrderHeader SOH
GROUP BY YEAR(OrderDate)) YearTotals

--total sales orders by sales rep per year
(SELECT YEAR(OrderDate) AS [Year]
, SalesPersonID
, COUNT(SalesOrderID) AS SalesPersonTotalOrders
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), SalesPersonID) YearTotalsBySalesPerson

ON YearTotals.[Year] = YearTotalsBySalesPerson.[Year]

Returning the results (sample)

Year        SalesPersonID SalesPersonTotalOrders TotalOrdersForYear PercentageOfSalesForYear
----------- ------------- ---------------------- ------------------ ---------------------------------------
2008        283           41                     901                4.55
2008        277           97                     901                10.77
2006        289           84                     1015               8.28
2007        279           158                    1524               10.37
2006        278           69                     1015               6.80
2007        285           9                      1524               0.59
2006        275           139                    1015               13.69
2008        286           56                     901                6.22
2005        274           4                      366                1.09
2008        280           12                     901                1.33
2005        282           51                     366                13.93

Derived tables can simplify code writing

Take the following example query which uses a CASE statement combined with GROUP BY

SELECT YEAR(OrderDate) AS [Year]
,CASE WHEN MONTH(OrderDate) IN (1,2,3) THEN 1
WHEN MONTH(OrderDate) IN (4,5,6) THEN 2
WHEN MONTH(OrderDate) IN (7,8,9) THEN 3
WHEN MONTH(OrderDate) IN (10,11,12) THEN 4 END AS [Quarter]
,COUNT(SalesOrderID) AS Orders
FROM Sales.SalesOrderHeader
,CASE WHEN MONTH(OrderDate) IN (1,2,3) THEN 1
WHEN MONTH(OrderDate) IN (4,5,6) THEN 2
WHEN MONTH(OrderDate) IN (7,8,9) THEN 3
WHEN MONTH(OrderDate) IN (10,11,12) THEN 4 END
ORDER BY [Year], [Quarter]

This can simplified with a derived table.

SELECT [Year], [Quarter], COUNT(SalesOrderID) AS Orders
(SELECT YEAR(OrderDate) AS [Year]
,CASE WHEN MONTH(OrderDate) IN (1,2,3) THEN 1
WHEN MONTH(OrderDate) IN (4,5,6) THEN 2
WHEN MONTH(OrderDate) IN (7,8,9) THEN 3
WHEN MONTH(OrderDate) IN (10,11,12) THEN 4 END AS [Quarter]
FROM Sales.SalesOrderHeader) YearQuarter
GROUP BY [Year], [Quarter]
ORDER BY [Year], [Quarter]

You can see that there is no need to write the CASE statement twice for the second version.


  • A derived table cannot be re-used within a query. Consider using a CTE or view in that instance.
  • A derived table is not a physical object within the database, it only exists at execution time.
  • Derived tables are not exclusive to SQL Server, other RDBMS systems support them, MySQL, Oracle, etc.
  • They are great for performing extra calculations on aggregated queries by enabling you to join sets of data together.
  • Can be used to help simply code writing, for example GROUP BY CASE WHEN statements as above.

10 Database Performance Monitoring Tools You Can Get For Free

Database performance monitoring is something every DBA worth their salt should be doing on a regular basis.It should be adopted as a proactive task to help identify issues early on before they become too serious and be part of a post code … [Continue reading]

When are Innodb Table Statistics Updated?

Innodb statistics are used by the query optimizer to assist it in choosing an efficient query execution plan. They are estimated values relating to each Innodb table and index.But what updates them? Let's take a look. Operations that update … [Continue reading]

How to Kill All MySQL Processes For a Specific User

So here is a scenario, you have a number of poorly performing MySQL database queries which are consuming resources on your server. Users are complaining and you need to do something fast. Having viewed the output of SHOW FULL PROCESSLIST, you can see … [Continue reading]

No MySQL SHOW USERS? – How to List MySQL User Accounts and Their Privileges

There are a number of useful SHOW commands in MySQL. Sadly, there isn't one for SHOW USERS.However all is not lost. You can get back a list of MySQL user accounts by querying the mysql.users table and if you want to find out which privileges each … [Continue reading]