Derived Table in SQL Server: Everything you need to know

Reading Time: 9 minutes

When learning about table expressions, the first and most simple table expressions you can learn is the Derived Table. They can help you gather the information you want quickly without needing to write much code.

Derived Tables are just one type of table expression we have available to us in SQL Server. For an introduction to all the different kinds of table expressions, check out the full beginner-friendly tutorial:

SQL Server Table Expressions: The Ultimate Guide for Beginners

In this tutorial, we will answer the following questions about derived tables:

What is a derived table?
What is the syntax for creating a derived table?
Examples of derived tables.
What are the limitations of a derived table?
Tips and tricks

Also, everything in this tutorial can be found in the following FREE Ebook:

FREE Ebook on SQL Server Table Expressions!

This Ebook provides an excellent summary of everything you need to know about the different table expressions we can create in Microsoft SQL Server, including derived tables. It will be a great resource for you to reference throughout your career as a data professional. Make sure you download it today!

Let’s start from the top.

1. What is a derived table?

A Derived Table is simply an inner query defined in the FROM clause of an outer query. This inner query is given an alias to represent the result set. A database object is not created when a derived table is defined, and the derived table falls out of scope when the outer query completes.

A derived table is a great solution if you are trying to gather information in a temporary kind of way. You can think of it as a View that doesn’t persist in the database.

2. What is the syntax for creating a derived table?

There isn’t much to it, really:

SELECT <column list> FROM (<inner SELECT statement>) AS <aliasName>

The ‘<aliasName>‘ serves as the name of your derived table, and you can treat it just like you would treat any other table. But remember, the scope of your derived table is the life of the outer query.

There are a few things you need to be aware of when it comes to the inner SELECT statement in a derived table:

In your inner SELECT statement, your columns must all have names. If one of your columns is the result of a calculation or an aggregation, for example, you need to make sure you give that column an alias.
Your column names must be unique. You cannot have two or more columns in your inner SELECT statement with the same name.
You cannot have an ORDER BY clause in your inner SELECT statement. If you read my discussion on Views, you will be familiar with this idea. The only way you could have an ORDER BY clause in your inner SELECT is if there was also TOP or OFFSET-FETCH used in your SELECT list.

3. Examples of derived tables.

Let’s finally look at some examples of derived tables.

Setting up some data

Take a look at the following Customers, Products, and Orders tables:

Here are the CREATE TABLE and INSERT statements so you can create this data in your environment and follow along:

CREATE TABLE Customers(
CustID int IDENTITY(50,5) NOT NULL,
FirstName varchar(20) NULL,
LastName varchar(20) NULL
)

CREATE TABLE Products(
ProductID int IDENTITY(20,2) NOT NULL,
ProductName varchar(20) NULL,
Price decimal(5, 2) NULL
)

CREATE TABLE Orders(
OrderID int IDENTITY(100,10) NOT NULL,
CustID int NULL,
ProdID int NULL,
Qty tinyint NULL,
Orderdate datetime NULL
)

INSERT Customers (FirstName, LastName) 
VALUES 
('Joshua', 'Porter'),
('Andrew', 'Bluefield'),
('Jack', 'Donovan'),
('Cindy', 'Thatcher'),
('Gordon', 'Acres'),
('Gretchen', 'Hamilton')

INSERT Products (ProductName, Price) 
VALUES 
('Large Bench', 198.00),
('Small Bench', 169.40),
('Coffee Table', 220.00),
('Side Tables', 265.20),
('Coat Rack', 45.00)

INSERT Orders (CustID, ProdID, Qty, Orderdate) 
VALUES 
(55, 22, 1, '2021-06-01'),
(60, 28, 2, '2021-06-06'),
(75, 26, 1, '2021-06-13'),
(50, 20, 1, '2021-07-01'),
(55, 28, 1, '2021-07-06'),
(65, 24, 1, '2021-07-14'),
(55, 26, 1, '2021-07-18'),
(50, 26, 1, '2021-07-24'),
(70, 24, 1, '2021-08-06'),
(70, 26, 1, '2021-08-06'),
(70, 22, 3, '2021-09-01')

Let’s think about the following SELECT statement (which does not use a derived table yet):

This SELECT statement uses an INNER JOIN to give us some great details about orders our company has fulfilled. Basically, each row tells us the following information:

The OrderID of the order
CustID of the Customer who bought the product
The product they purchased
The sale price of the order, which is the price of the product multiplied by how many they bought.
The date the order was placed

An example of a derived table

Let’s say we were only interested in order information where the SalePrice was greater than 200.00 dollars.

It would be nice if we could add a WHERE clause to the query above. We could write the following WHERE clause:

But if you think about it, this creates a situation where we are repeating work. In order to filter on Orders greater than $200.00, we need to multiply the Price by the Qty, which is something we already did once in the column list:

It’s never a good thing to repeat code. And no, we can’t use the ‘SalePrice’ alias in the WHERE clause. You can only use aliases in an ORDER BY clause.

This is a perfect example of when we could use a derived table. I’ll show you what it looks like, then we’ll talk about it:

Let’s break down what we have here.

The SELECT statement from earlier is now in an inner query. The result set of that inner query is what is used for our derived table called OrderInfo. Here is the result set of just that inner query again:

In our outer SELECT statement, when we pick columns from the OrderInfo derived table, these are the only columns we can choose from. So that’s these columns:

OrderID
CustID
ProductName
SalePrice
Orderdate

Our outer query uses just the OrderID, CustID, and Saleprice columns, for example.

Naming columns in your derived table

The next thing to notice about the derived table is how all the columns have names. Again, this is a requirement of all derived tables (and all other table expressions, in fact).

We had to give an alias for the calculation of our sale price (Qty*Price), for example. That alias is, of course, ‘SalePrice’

We also had to give our derived table an alias. We called it ‘OrderInfo‘. I’ve seen other developers choose to make the alias a single letter (for example, ‘O‘), but I think that’s simply bad practice. You want the names of your table expressions to make sense and be descriptive.

The letter ‘O‘ could be anything. Maybe ‘Orangutan‘?

Next, notice the WHERE clause. Again, you would treat a derived table the same way you would treat a regular table, and a regular table can obviously have a WHERE clause applied to it. Since we want to filter our results to only see SalePrice greater than $200.00, we can easily set that up.

A derived table can be treated like any other table

The final thing I’d like to point out is something I touched on in the last point: You would treat a derived table the same way you would treat a regular table. This means, for example, we could use this derived table in one side of a JOIN.

Let’s say we wanted to see the first and last name of the customers who spent more than $200.00. We can easily set up a JOIN with the Customer table in our outer SELECT statement:

You can use a derived table anywhere a table is expected!

4. What are the limitations of a derived table?

As I said earlier, the scope of a derived table is the duration of the outer query.

….so not very long.

A table object is not persisted in the database, so the derived table cannot be used in any other query. To drive the point home, we cannot do this:

It doesn’t matter that I ran both queries in the same batch. The two queries are just that: TWO QUERIES!

To reiterate something else I said earlier, derived tables also cannot have an ORDER BY clause. The outer query can, but the inner query cannot. Again, to drive the point home:

Here is that full error message:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

If you wanted your results ordered by CustID, like I just attempted to do, you would need to specify your ORDER BY clause in the outer query:

Derived tables might not always be the best solution

The last thing I want to point out is how a derived table might not always be the cleanest solution. For example, let’s look at the following query (which does not use a derived table yet):

This query uses the GROUP BY clause to tell us how many times a customer has purchased from us. It looks like Joshua Porter has bought from us twice, and both Andrew Bluefield and Gordon Thatcher have bought from us 3 times. Everyone else has bought from us only once.

So what if we wanted to see only the customers who have bought from us more than once (aka the repeat customers)?

We could set it up using a derived table, like this:

This works fine, but understand we could have achieved the same result set by using the HAVING clause. There is no denying the following query is shorter:

Same result, less code.

So realize derived tables might be more work than other SQL solutions.

5. Tips and tricks

Here is a list of some tips and tricks you should know when working with derived tables.

Tip #1: Column aliases can be assigned internally or externally

Remember, a unique column name must be given to all columns in the inner SELECT statement. If you have a column based on a calculation or an aggregation (like in my examples above), there are two ways you can add an alias to the column: Internally and Externally.

My examples above all use internal aliasing, meaning the alias is defined directly next to the column in the inner SELECT list.

Here is that first simple example of a derived table again:

We created an alias called ‘SalePrice’ for the result of Qty*Price. This alias is outlined in the inner SELECT list, directly next to that calculation.

If we wanted to outline our column names externally, we would do it like this:

To outline the column names of your derived table externally, you outline those names in parentheses next to your derived table name, from left to right.

Notice we need to outline all the columns, and not just the one that is the result of a calculation. This is one of the reasons I don’t support external aliasing. You are essentially duplicating code. Also, in my opinion, it just doesn’t read as cleanly. But understand it can be done.

By the way, if you put aliases both externally and internally (because you like to watch the world burn) the external aliases will take precedence.

Tip #2: You can run the inner query by itself, which can make troubleshooting easier

To easily see the result set of the inner query, you can just highlight that inner query and run it by itself. We did that earlier:

Sometimes, when you are troubleshooting an issue, it’s nice to have the ability to highlight the inner query to see exactly what it returns.

Tip #3: Your ORDER BY clause must be against the outer query

Remember, an ORDER BY clause cannot be used by itself in your inner query. If you need your results put in a specific presentation order, you would specify your ORDER BY clause in your outer query.

Tip #4: Derived tables are NOT created as permanent objects in the database

As a reminder, derived tables are not created as objects in the database, so they cannot be found in the Object Explorer of SQL Server Management Studio. Remember, the scope of a derived table is only within the query it is written.

Next Steps:

Leave a comment if you found this tutorial helpful!

Don’t foget to download your FREE Ebook:

FREE Ebook on SQL Server Table Expressions!

Do you know what a SQL Server View is? Click the link to find out!

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, leave a comment. Or better yet, send me an email!

Derived Table in SQL Server: Everything you need to know

SQL Server Table Expressions: The Ultimate Guide for Beginners

FREE Ebook on SQL Server Table Expressions!

1. What is a derived table?

2. What is the syntax for creating a derived table?