What is 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"
 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!