Derived tables in SQL server are a dynamic data source for ‘select’ statements similar to a sub query structure but unlike a sub query a derived table exists in the ‘from’ clause and can be joined to as you would do to a normal table or view.
Consider the following simple example, we have two tables one for products and the other for product sales
CREATE TABLE [dbo].[tblProduct]( [ProductID] [int] IDENTITY(1,1) NOT NULL, [ProductName] [nvarchar](50) NOT NULL, [ProductDescription] [nvarchar](100) NULL, CONSTRAINT [PK_tblProduct] PRIMARY KEY CLUSTERED ( [ProductID] ASC ) CREATE TABLE [dbo].[tblSales]( [ProductID] [int] NOT NULL, [QtySold] [int] NOT NULL, [DateSold] [datetime] NOT NULL, [UnitPrice] [decimal](18, 2) NOT NULL ) ON [PRIMARY]
In the example we are going to create a query that will sum the total sales price for items and then display the list of products whose sales are greater than 5000. The query is going to use a derived table to do the aggregation and then we will join the derived table to tblProduct so that we can display the product details
SELECT prod.ProductName, prod.ProductDescription, Sales.SalesVolume FROM tblProduct prod JOIN ( SELECT sal.ProductID,SUM(sal.QtySold * sal.UnitPrice) AS [SalesVolume] FROM tblSales sal GROUP BY sal.ProductID) as Sales ON prod.ProductID = Sales.ProductID WHERE Sales.SalesVolume > 5000
As you can see in the example we are able to access columns from the derived table in our outer ‘select’ statement like we can with any joined table. Derived tables always need to be enclosed within brackets and always need to be aliased as we have done above, all columns being exposed by the derived table need to either have a name or alias name.
As a comparison we could also have accomplished the above query by using a ‘group by’ query as in the below example
SELECT prod.ProductName, prod.ProductDescription, SUM(sal.QtySold * sal.UnitPrice) AS [SalesVolume] FROM tblSales sal JOIN tblProduct prod ON sal.ProductID = prod.ProductID GROUP BY prod.ProductName, prod.ProductDescription HAVING SUM(sal.QtySold * sal.UnitPrice) > 5000
Both query types are valid and in terms of performance when summing up 4 million rows in the tblSales table the end result is more or less the same, but from a usability point of view I always find derived tables for aggregation queries that little easier to work with.