This tutorial will show you how to create and use a derived table in SQL Server. Derived tables, also known as subqueries, are defined in the FROM clause of an outer query. Their scope of existence is in the outer query, when the outer query is finished the derived table goes away. A derived table is defined in parenthesis followed by an AS clause to specify the derived table name. Think of the query as having a table within a table, we use a derived table to get a result set and once we have it we eliminate it when the outer query is finished.

Setting Up

For this tutorial, we will use a table called Employee that will contain columns of EmployeeId, LastName, Country, and DepartmentId. To create the table we will create a query that uses the CREATE TABLE statement.

After creating an Employee table, lets add data into it with the INSERT INTO statement.

Lets run a quick query to get a look at the table entries. Running ‘SELECT * FROM Employee’ retrieves the data in the Employee table.

Derived Table

Now that we have created an Employee table with entries, we can now create a query that works with a derived table. As stated earlier, a derived table is best thought of as a table within a table that exists only as long as the outer query does. Keep that in mind as it is being created as it will help with understanding it.

In this query we will select the LastName and EmployeeId columns from the derived table. Normally we would select a table in the database, but in this case we create a special table for it. To create a derived table, we use the SELECT and FROM statements in parenthesis to select all columns in the Employee table. We then use the WHERE clause to filter entries from ‘Australia’ and name the derived table ‘EmployeeDerivedTable. The derived table is now finished but the outer query still has minor things to specify. We use the WHERE clause to select entries with a DepartmentId of ’33′ and then to order the results by LastName.

What we are doing is first getting the result set from the derived table (the SELECT statement in the FROM clause). Once we have that result set, we can perform the SELECT on the derived table, returning our results.

Output

Execute the query and there will be two results that match the description of being in Austraila and having a DepartmentId of 33.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.

Download Source Files