SQL: Derived Tables in SQL


Derived Tables in SQL

The power of SQL Server never fails to amaze me; it literally seems that you can do just about anything in SQL. All you need is a little creativity and knowledge of the syntax, and you can put the power of SQL behind your web application. One of the neatest things I’ve yet done with SQL Server is using derived tables.

If you’ve used a VIEW before, you’ve used a more formal, more correct form of a derived table. For example, we could do the following:

 CREATE VIEW vwEmployeesFromNewYork AS
 SELECT * FROM Employee
 WHERE State = "NY"

Then if we wanted to see all of the Employees from New York with the last name Smith, ordered alphabetically, we could write:

 SELECT LastName, FirstName
 FROM vwEmployeesFromNewYork
 WHERE LastName = "Smith"
 ORDER BY FirstName

However, using derived tables, we could eliminate the view entirely. (Of course the view could be eliminated by simply adding an "AND State = "NY"" to the above WHERE clause, but what’s important here is the concept, not the example!) Here is the same resultset as above but with the use of a derived table in place of a veiw:

 SELECT LastName, FirstName
 (SELECT * FROM Employee
 WHERE State = "NY") AS EmployeeDerivedTable
 WHERE LastName = "Smith"
 ORDER BY FirstName

Isn’t that neat? What we are doing is first getting the result set from our derived table (the SELECT statement in the FROM clause). Once we have that resultset, it is as though it was a table in itself. We then perform the SELECT on the derived table, returning our results! You can find another example of using derived tables here on 4GuysFromRolla.com in the article Obtaining Ranked Values from a Table page.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s