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
(SELECT *
FROM Table) Table1

So lets put the syntax to something more real:


USE AdventureWorks2012
GO
SELECT TOP 10 FirstName, LastName
FROM
(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
GO

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

JOIN
--total sales orders by sales rep per year
(SELECT YEAR(OrderDate) AS [Year]
, SalesPersonID
, COUNT(SalesOrderID) AS SalesPersonTotalOrders
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
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
GROUP BY
YEAR(OrderDate)
,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
FROM
(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]
,SalesOrderID
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.

Summary

  • 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]

List Tables for a Specific Database Using MySQL SHOW TABLES

This post demonstrates how to list all tables for a specific MySQL database. I will use SHOW TABLES for this. First, login as user root to your MySQL instance. [sourcecode language='sql'] mysql -uroot -pYourPassword [/sourcecode] Now … [Continue reading]

A Quick Guide to MySQL SHOW DATABASES

In this quick guide, I demonstrate how to show a list of databases in MySQL using SHOW DATABASES. First, login as user root to your MySQL instance. [sourcecode language='sql'] mysql -uroot -pYourPassword [/sourcecode] Now that you are … [Continue reading]

Using SELECT TOP WITH TIES in SQL Server

I've used SELECT TOP (x) many, many times. After learning how to use SELECT, it's something I quickly learned to use and remains something which I continue to use often. I've also used TOP (x) with UPDATE and DELETE statements to limit the number … [Continue reading]

How to Resize MySQL Innodb Log Files Without Errors

Upon installing MySQL, the default innodb log file size is 5MB. This might be fine for a lot of servers but should you want to resize it, it is not simply a case of changing innodb_log_file_size to the new size and restarting MySQL. MySQL will not … [Continue reading]

Comparing Ways to Get Table Row Counts in MySQL and SQL Server

Get Row Counts MySQL SQL Server

I work in a mixed database server environment where applications are either powered by SQL Server, MySQL and more recently MongoDB. I started my career as a DBA working with SQL Server back in around 2003. In the last 5-6 years I have also been … [Continue reading]

How to Install MySQL 5.5 on Ubuntu Server 12.04 LTS

For this how to, I am going to document how to install MySQL on an installation of Ubuntu 12.04 LTS Installing it is a very straight forward and quick process and I prefer to use the terminal. If you are running a GUI, you can also install it … [Continue reading]