SQL Server Table Expressions: The Ultimate Guide for Beginners

There are several different kinds of table expressions you need to be aware of when learning to query and develop SQL Server databases. Each kind of table expression has it’s own strengths and weaknesses, and it is important for you to understand those strengths and weaknesses so you know which is best to use in a given situation.

In this tutorial, we’ll discuss the four most common table expressions: Derived Tables, Common Table Expressions (CTE’s), Views, and Inline Table Valued Functions.

This is a general overview tutorial, but to see the in-depth discussions on each, check out these links:

• SQL Server Derived Tables: Everything you need to know
• Common Table Expressions (CTE’s): Everything you need to know
• What is a View in SQL Server? A Guide for Beginners
• Inline Table Valued Functions: The Ultimate Guide for beginners

In this tutorial, we will cover the following topics:

1. What is a table expression in SQL Server?
2. When should we use a table expression?
3. Derived Tables
4. Common Table Expressions
5. Views
6. Inline Table Valued Function
7. Rules to remember about Table Expressions

The information found in this tutorial can also 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. 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 table expression in SQL Server?

A table expression is basically a named query whose result set meets all the criteria of being it’s own table, but is virtual. Table expressions allow you to basically create your own custom, virtual table without creating or changing any existing physical tables.

When we say a table expression is virtual, we mean there isn’t a physical table object created anywhere in the database. With a table expression, the columns and rows are all picked and pulled from actual tables to give you a Frankenstein-ish virtual table.

Now that I think about it, Frankenstein’s monster is a great analogy.

Frankenstein’s monster was basically pieces of other men combined to make an entirely new man.

But was he an actual man?

Well, no, because he wasn’t born like a real man is.

But at the same time, he walks, talks, dances, etc. like a real man does. He even feels emotion like a real man, and therefore, should be treated as a human being despite being different 🙂

This idea is the essence of a table expression.

Let’s look at the result set of a table expression:

This result set is showing us some basic details about Orders in our database. We see the Order ID, the First and Last name of the person who made the order, and how much they paid for the product(s) they purchased.

There’s a reason I’m not showing you the query that generated the result set: It’s not important right now. I just want to show you what makes this result set a table expression.

Think about it. I mean, this sorta looks like it could be it’s own table, right?

• It has columns and rows
• All rows are unique
• All column names are unique

But again, this isn’t it’s own table. It pulls all the columns and data from other tables, making it a table expression. 

2. When should we use a table expression?

Table expressions are great in the following scenarios:

• Meaningfulness – You can pick and pull information into a table expression to see data presented in a way that is meaningful to you in your given situation, better than each individual table can.
• Simplicity – Sometimes, the use of a table expression makes writing other queries easier.
• Reusability – There are some table expressions that can be saved as objects in your database. If the table expression is saved, you can reuse it whenever you need to!
• Impermanence –  On the other end, sometimes you want your table expression to be temporary. Maybe you specifically don’t want a persisting database object to be created. There are other table expressions that are good for that, too.

3. Derived Tables

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 one of those table expressions that is temporary. It falls out of scope as soon as the outer query completes.

Here is the syntax of a derived table:

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

The <inner SELECT statement> returns a result set, and that result set is used as the virtual table in the FROM clause of the outer query. The <aliasName> is the name we give to that inner result set. Remember, a table expression is a named query.

An example of a derived table

Here is an example of a simple Derived table:

See how the inner query exists in the FROM clause of the outer query. This inner query is given an alias of OrderInfo. 

As soon as the outer query finishes, the scope of the derived table ends. We cannot use OrderInfo in another query, for example.

Here is the link for an in-depth look at Derived Tables:

SQL Server Derived Tables: Everything You Need to Know

4. Common Table Expressions

A Common Table Expression (or CTE) is a temporary table expression that defines an inner query whose result set will be used in the FROM clause of an outer query. The inner query is defined and given a name before the outer query, and the outer query references that name in it’s FROM clause. 

CTE’s are another temporary table expression.

Here is the basic syntax of a CTE:

;WITH <CTE alias name>
AS
(
<inner query>
)
SELECT <column list> FROM <CTE alias name>

No, the semicolon in front of the word ‘WITH‘ is not a typo. The word ‘WITH‘ is a keyword in SQL Server, so when we use it specifically for a CTE, we need to terminate any preceding statements with a semicolon as to not confuse SQL Server. The easiest way to do that is to just put a semicolon directly in front of the word ‘WITH‘

An example of a CTE

Here is an example of a CTE:

I want to point out the outer query:

SELECT * FROM CustomersWithEmail

This isn’t any different from how you would query a regular table. The table name (whether it’s virtual or real), is in the FROM clause.

This is one of the reasons I like using CTE’s. The outer query simply reads a bit cleaner, in my opinion.

Since CTE’s are another temporary table expression, it means the CTE falls out of scope when the outer query completes and it cannot be used in a different query.

For the full discussion on Common Table Expressions, check out this link:

Common Table Expressions (CTE’s): Everything you need to know

5. Views

A View is a virtual table object that pulls columns and data from one or more tables. Unlike Derived Tables or CTE’s, a View is created as an object in the database. This means a View can be used to store a complex SQL query so that it can be ran repeatedly using simple commands.

Views are permanent objects, in the sense that they don’t fall out of scope when the query that uses it completes. You can reference a View in as many queries as you want!

Here is the basic syntax for how to create a View:

CREATE VIEW viewName
AS
<SELECT statement>

Here is an example of a View:

The inner query of this View will pull information about customers who have purchased items from us more than once (hence the name RepeatCustomerInfo).

Views allow us to save complex queries

Notice how complicated the inner query is. It would be difficult to remember this query if we wanted to get an update on who our repeat customers are.

Here is something you should remember about the creation of Views: When you create the View, it doesn’t actually run the inner query and return a result set to you. Here’s what I mean:

I ran this code and it simply returned ‘Commands completed successfully‘.

The CREATE VIEW statement does just that: It creates the View, it doesn’t call it. 

To call the View, you would outline the name of the View in the FROM clause of a query, like so:

See how easy that is? That overly-complicated inner query is now hidden, and we can still see it’s results using a much easier outer query.

And again, we can query this View anytime, anywhere we want. The life of the View is as long as it exists in the database. The only way to remove a View after is has been created is to physically delete it from the database (DROP VIEW <view name>).

For the full discussion on Views, take a look at the full tutorial:

What is a View in SQL Server? A Guide for Beginners

6. Inline Table Valued Functions

An Inline Table Valued Function returns a result set to the caller by way of an inner SELECT statement that is part of the function definition. You can query an Inline Table Valued function similar to how you would query a table. You can also pass parameters to an Inline Table Valued function, allowing it to return a different result set depending on the parameters passed in.

An Inline Table Valued Function is very similar to a View. It is a permanent object created in the database that can be ran repeatedly.

The advantage an Inline Table Valued Function has over a View is how you can pass in parameters, allowing it to return different results depending on your parameters.

In fact, another name for an Inline Table Valued Function is a Parameterized View.

Here is the syntax for how you would create an Inline Table Valued Function:

CREATE FUNCTION <schema>.function_name(<optional parameters>)
RETURNS TABLE
AS
RETURN
<SELECT statement>

An example of an Inline Table Valued Function

Here is an example of an Inline Table Valued Function:

This function basically returns details about how much money a specific Customer (whose ID is passed in) has spent on our products.

Again, there is a difference between creating the function and calling the function. The screenshot above just shows us creating the function.

Again, notice how complicated the inner query is. It would be a nightmare to try to remember this query if we needed to run it multiple times for different customers.

Here is how this function is called:

We pass in the Customer ID, and it returns some details about that customer and how much money they have spent with us.

Pretty neat, right?

When you call a function, you need to specify the schema the function belongs to

When I called the function, you might have noticed how I outlined the schema the function belongs to, dbo. Whenever you call a function, you need to specify the schema it belongs to. If you don’t, you will get an error message.

Just like a View, we can query this Inline Table Valued Function anytime, anywhere we want. It’s life is as long as it exists in the database. The only way to remove an Inline Table Valued Function is to physically delete it from the database (DROP FUNCTION <function name>).

To see the full discussion on Inline Table Value Functions, follow this link:

Inline Table Valued Functions: The Ultimate Guide for Beginners

7. Rules to Remember about Table Expressions

There are a few important rules you need to remember that apply to all the different table expressions, specifically to the inner query of the table expression:

1. 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, you need to make sure you give that column an alias.
2. In your inner SELECT statement, your column names must be unique. You can’t have two or more columns in your inner SELECT statement with the same name.
3. You can’t have an ORDER BY clause in your inner SELECT statement. The only exception is if there was also TOP or OFFSET-FETCH used in your SELECT list, but normally you don’t want to limit your results in that way. If you need your results presented in a specific order, you need to specify the ORDER BY clause in the outer query.
4. The inner query can be only one query. You can’t have two SELECT statements in the inner query of a derived table, for example.

Next Steps:

Leave a comment if you found this tutorial helpful!

Don’t forget to download your 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. It will be a great resource for you to reference throughout your career as a data professional. Make sure you download it today!

This tutorial is meant to be an introduction to table expressions. To get the full rundown on all the different types of table expressions, you should check out these links:

• SQL Server Derived Tables: Everything you need to know
• Common Table Expressions (CTE’s): Everything you need to know
• What is a View in SQL Server? A Guide for Beginners
• Inline Table Valued Functions: The Ultimate Guide for beginners

Also, there is a great book called T-SQL Fundamentals written by Itzik Ben-Gan that goes over several core concepts you should know about SQL Server, including table expressions. The book actually has an entire chapter dedicated to the discussion of table expressions. You won’t regret owning this book, trust me. Definitely get it today!

Thank you 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!