Common table expressions (CTE for short) in SQL in many respects can be thought of as a shorthand way of building a limited scope temporary table variable from a select statement and they are great for splitting up complex queries into smaller bite size chunks.

In a previous post we looked at using derived tables and the below examples builds upon the same theme as the examples in that post, using the same tables and data.

CTE are defined by using a ‘with’ command followed by a name that you will refer to the CTE by in a preceding SQL statement, within the main body of the CTE is the select statement that will supply the data into the CTE.

WITH MyTemp
AS(SELECT........)

CTE’s are useful when you want to be able to work with aggregated data especially when the aggregation involves an inline function, in the below example we create a table expression called MyTemp and assign to it a record source which does some data aggregation.

There is no need to declare MyTemp it will be created for us by the WITH statement and once we have our CTE defined we can work with it as we would any other table, for example if we wanted to return all records where the sales volume is greater than 5000 then we could make a select statement as below,

SELECT*FROM MyTemp where SalesVolume >5000

Now for the important part, the scope of a CTE is only valid for the SQL statement immediately after the CTE definition and if we try to call the CTE from anywhere else we will get an error, for example the below code would return an error as the select from MyTemp is not directly after the WITH definition.

In the above example the CTE will use the column names from the select statement that feeds it the data, but if we want to we are able to override these column names by defining within the definition as per the below example where we define the column names “Product,Description,TotalProductVolume”.

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

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

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

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.