Using Derived Tables to Simplify the SQL Server Query Process

By:   |   Comments (13)   |   Related: > Query Optimization


Problem

Sometimes querying data is not that simple and there may be the need to create temporary tables or views to predefine how the data should look prior to its final output.  Unfortunately there are problems with both of these approaches if you are trying to query data on the fly. 

With the temporary tables approach you need to have multiple steps in your process, first to create the temporary table, then to populate the temporary table, then to select data from the temporary table and lastly cleanup of the temporary table.

With the view approach you need to predefine how this data will look, create the view and then use the view in your query.  Granted if this is something that you would be doing over and over again this might make sense to just create a view, but let's look at a totally different approach.

Solution

With SQL Server you have the ability to create derived tables on the fly and then use these derived tables within your query.  In concept this is similar to creating a temporary table and then using the temporary table in your query, but the approach is much simpler, because it can all be done in one step.

Let's take a look at an example where we query the Northwind database to try to find out how many customers fall into various categories based on sales.  The categories that we have predefined are as follows:

  • Total Sales between 0 and 5,000 = Micro
  • Total Sales between 5,001 and 10,000 = Small
  • Total Sales between 10,001 and 15,000 = Medium
  • Total Sales between 15,001 and 20,000 = Large
  • Total Sales > 20,000 = Very Large

There are several ways that this data can be pulled, but let's look at an approach using a derived table.

The first step is to find out the total sales by each customer, which can be done with the following statement.

SELECT   o.CustomerID, 
         SUM(UnitPrice * Quantity) AS TotalSales 
FROM     [Order Details] AS od 
         INNER JOIN Orders AS o ON od.OrderID = o.OrderID 
GROUP BY o.CustomerID

This is a partial list of the output:

CustomerID TotalSales
ALFKI  4596.2000
ANATR 1402.9500
ANTON 7515.3500
...  
WOLZA 3531.9500

The next step is to classify the TotalSales value into the OrderGroups that were specified above:

SELECT   o.CustomerID, 
         SUM(UnitPrice * Quantity) AS TotalSales, 
         CASE  
           WHEN SUM(UnitPrice * Quantity)  
               BETWEEN 0 AND 5000 THEN 'Micro' 
           WHEN SUM(UnitPrice * Quantity)  
               BETWEEN 5001 AND 10000 THEN 'Small' 
           WHEN SUM(UnitPrice * Quantity)  
               BETWEEN 10001 AND 15000 THEN 'Medium' 
           WHEN SUM(UnitPrice * Quantity)  
               BETWEEN 15001 AND 20000 THEN 'Large' 
           WHEN SUM(UnitPrice * Quantity)  
               > 20000 THEN 'Very Large' 
         END AS OrderGroup 
FROM     [Order Details] AS od 
         INNER JOIN Orders AS o ON od.OrderID = o.OrderID 
GROUP BY o.CustomerID 

This is a partial list of the output:

CustomerID TotalSales OrderGroup
ALFKI  4596.2000 Micro
ANATR 1402.9500 Micro
ANTON 7515.3500 Small
...    
WOLZA 3531.9500 Micro

The next step is to figure out how many customers fit into each of these groups and this is where the derived table comes into play.  Take a look at the following query which uses a derived table called OG.  What we are doing here is using the same query from the step above, but calling this derived table OG. Then we are selecting data from this derived table for our final output just like we would with any other query.  All of the columns that are created in the derived table are now available for our final query.

SELECT   OG.OrderGroup, 
         COUNT(OG.OrderGroup) AS OrderGroupCount 
FROM     (SELECT   o.CustomerID, 
                   SUM(UnitPrice * Quantity) AS TotalSales, 
                   CASE  
                     WHEN SUM(UnitPrice * Quantity)  
                       BETWEEN 0 AND 5000 THEN 'Micro' 
                     WHEN SUM(UnitPrice * Quantity)  
                       BETWEEN 5001 AND 10000 THEN 'Small' 
                     WHEN SUM(UnitPrice * Quantity)  
                       BETWEEN 10001 AND 15000 THEN 'Medium' 
                     WHEN SUM(UnitPrice * Quantity)  
                       BETWEEN 15001 AND 20000 THEN 'Large' 
                     WHEN SUM(UnitPrice * Quantity)  
                       > 20000 THEN 'Very Large' 
                   END AS OrderGroup 
          FROM     [Order Details] AS od 
                   INNER JOIN Orders AS o ON od.OrderID = o.OrderID 
          GROUP BY o.CustomerID) AS OG 
GROUP BY OG.OrderGroup 

This is the complete list of the output from the above query.

OrderGroup OrderGroupCount
Large 10
Medium 11
Micro 33
Small 15
Very Large 20
Next Steps
  • Next time you run into a challenge of whether to create a temporary table or a view to produce the desired query take a look at using a derived table instead
  • Experiment with using derived tables, views and temporary tables to see what yields better performance results. There are several articles on the internet that have shown that using a derived table is faster then temporary tables, but try it for yourself.
  • Take a look at Common Table Expressions


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, September 28, 2017 - 4:31:23 AM - Tom Boles Back To Top (66674)

 This helped a lot - and very easy to understand

 


Friday, April 22, 2016 - 10:06:23 AM - Bill Ross Back To Top (41309)

 You might want to mention the performance difference between a temp table (fast) vs derived table (slow).

 


Monday, February 8, 2016 - 2:48:53 AM - saikrishna Back To Top (40618)

@Frank

 

SELECT *

FROM EmdCodes

WHERE Description ="%CHESTPAIN%";

 

Instead of using '=' in the Query use like operator that will solve ur problem

 

SELECT *

FROM EmdCodes

WHERE Description like "%CHESTPAIN%";


Monday, December 1, 2014 - 1:51:40 AM - H. singh Back To Top (35435)

Informative


Wednesday, December 11, 2013 - 10:48:26 AM - BuPhilOh Back To Top (27765)

Simple, elegant, informative.  Just what I needed as a newbie bumping into the problem of how to do calculations based on the results of other calculations.  Thank you, Greg!  Now to see if I can put it to work....


Monday, April 1, 2013 - 3:36:42 PM - Greg Robidoux Back To Top (23103)

@George - not totally sure if a CTE will perform better.  The best option is to test both methods and see what is eaiser to setup and also what performs better.


Monday, April 1, 2013 - 3:35:27 PM - Greg Robidoux Back To Top (23102)

@Frank - I think you just need a space in your query:

SELECT *

FROM EmdCodes

WHERE Description ="%CHEST PAIN%";


Monday, April 1, 2013 - 11:01:43 AM - RHG Back To Top (23099)

Very helpful, thanks a lot


Wednesday, March 6, 2013 - 2:50:38 PM - George Back To Top (22606)

Any thoughts on whether using a Common Table Expression is better than a derived table?


Sunday, March 3, 2013 - 11:52:24 AM - Frank Back To Top (22538)

Im am trying to create a sql query that will return the following (1)  from the table snippet (2)  I am using this (3):

(1)

EmdDescription

10D110 - CHEST PAIN - D1 Severe Respiratory distress

10D210 - CHEST PAIN - D2 Not alert

10D310 - CHEST PAIN - D3 Clammy

 

(2)

EmdDescription

10D110 - CHEST PAIN - D1 Severe Respiratory distress

10D210 - CHEST PAIN - D2 Not alert

10D310 - CHEST PAIN - D3 Clammy

11A111 - CHOKING - A1 Not choking now

11D111 - CHOKING - D1 Not alert

11D211 - CHOKING - D2 Abnormal breathing

11011 - CHOKING - E1 Choking verified or ineffective breathing

12A112 - CONVULSIONS SEIZURES - A1 Not seizing now regularly

12B112 - CONVULSIONS SEIZURES - B1 Breathing regular not verified < 35 yrs

 

SELECT *

FROM EmdCodes

WHERE Description ="%CHESTPAIN%";

 

Any help is appreaciated.

 

 

 

 


Tuesday, June 19, 2012 - 7:37:13 AM - Sulthanul Arifeen Back To Top (18099)

Derived tables only helpful for small amount data tranction. For huge data transaction with calculation, temp table is better option.  My suggestion is to use temporary table whenever possible only keeping the locking issue under consideration.


Tuesday, December 21, 2010 - 1:31:54 PM - Greg Robidoux Back To Top (10480)

Hi Jackie, what version of SQL Server are you using?

Since all of the data is GROUPed you will need to include any column that is not a summary such as AVG, SUM, COUNT, etc... in the GROUP BY clause.

Also, I am not sure why the AS for the derived table did not work.  This is not really necessary.  I tried this both with and without the AS and it worked fine both ways.


Tuesday, December 21, 2010 - 12:33:41 PM - Jackie Bird Back To Top (10478)

Tried your derived table example - but when you have multiple fields to pull you have to include all of them in the group by clause - moreover the AS for the inner group by doesn't work - it throws an error and when i remove it the sql executes















get free sql tips
agree to terms