Derived Tables

for RuBoard

Derived tables are SELECT statements that you embed within the FROM clause of other SELECTs in place of table references. I include coverage of them here for completeness and because they resemble implicit or automatic views. Derived tables make certain types of queries possible that previously required separate view objects. Listing 9-12 presents an example:

Listing 9-12 A derived table can be used in many instances when you'd use a temporary view.

 CREATE TABLE #1996_POP_ESTIMATE (Region char(7), State char(2), Population int) INSERT #1996_POP_ESTIMATE VALUES ('West',   'CA',31878234) INSERT #1996_POP_ESTIMATE VALUES ('South',  'TX',19128261) INSERT #1996_POP_ESTIMATE VALUES ('North',  'NY',18184774) INSERT #1996_POP_ESTIMATE VALUES ('South',  'FL',14399985) INSERT #1996_POP_ESTIMATE VALUES ('North',  'NJ', 7987933) INSERT #1996_POP_ESTIMATE VALUES ('East',   'NC', 7322870) INSERT #1996_POP_ESTIMATE VALUES ('West',   'WA', 5532939) INSERT #1996_POP_ESTIMATE VALUES ('Central','MO', 5358692) INSERT #1996_POP_ESTIMATE VALUES ('East',   'MD', 5071604) INSERT #1996_POP_ESTIMATE VALUES ('Central','OK', 3300902) SELECT * FROM (SELECT TOP 5 WITH TIES State,               Region, Population=Population/1000000              FROM #1996_POP_ESTIMATE              ORDER BY Population/1000000) p ORDER BY Population DESC State Region Population ----- ------- ----------- NJ    North   7 NC    East    7 WA    West    5 MO    Central 5 MD    East    5 OK    Central 3 

This query uses a derived table to return the five states with the lowest population among those listed in the table. It then uses the ORDER BY in the outer SELECT to sort them in descending order. Were it not for derived table support, this approach would require a separate stand-alone view or a temporary table.

One subtlety worth mentioning here is the requirement of a table alias when using derived tables. Note the inclusion of the table alias in Listing 9-12 even though it's not used. This is a requirement of derived tables, regardless of whether your code actually uses the alias.

for RuBoard