Windows Systems Administrator Jupitermedia US-CT-Darien Post A Job | Post A Resume

Derived Tables in SQL
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 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! You can find another example of using derived tables here on 4GuysFromRolla.com in the article Obtaining Ranked Values from a Table page.

Happy Programming!

By Scott Mitchell

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article