By Scott Mitchell
* This article discusses the uses of derived tables in SQL Server.
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
CREATE VIEW vwEmployeesFromNewYork AS SELECT * FROM Employee WHERE State = "NY" GO
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 FROM (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.