• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

T-SQL – Derived Tables Demonstrated and Explained

October 18, 2014 by Andy Hayes Leave a Comment

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.

Related Posts:

  • sql grouping sets
    Using SQL GROUPING SETS for Multiple GROUP BY…
  • How to fix "conversion failed when converting date and/or time from character string"
    How to fix "conversion failed when converting date…

Filed Under: All Articles, SQL Tips and Tricks Tagged With: sql, sql server, t-sql

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Categories

  • All Articles (84)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (21)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to fix “conversion failed when converting date and/or time from character string”

Recent Posts

  • How to fix “conversion failed when converting date and/or time from character string”
  • Using SQL GROUPING SETS for Multiple GROUP BY Queries in a Single Query
  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial

Search

Connect

  • Twitter
  • Facebook
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©