Skip Navigation LinksHome > Categories > Code from a Category

How to Create and Use a Derived Table in SQL Server



User Name: codelecturer
Name: Mike Chauhan
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 1939
Add Date: 12/06/2012
This article 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.

Create the Employee table.

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

Add data entries into the table.

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.

Create a derived table.

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.

Happy Programming!!!

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.