 |
|
SQL Server Tips by Burleson |
Derived table
A derived table is a select statement inside parenthesis, with an
alias, used as a table in a join or union. Derived tables are very
common with JOIN clauses because they have a defined name, which is
necessary for the join, unlike subqueries. They are an alternative
to temporary tables in the same situations as subqueries. Another
use for derived tables is in row calculations, particularly when
there are excessive aggregate functions and CASE statements.
Example: same as the previous one.
SELECT TOP 100 PERCENT
dbo.titles.title, dbo.titles.royalty
FROM dbo.titles
INNER JOIN
(SELECT (min(royalty)+max(royalty))/2 as avgTotal FROM dbo.roysched
) r2
on dbo.titles.royalty> avgTotal
ORDER BY dbo.titles.title
This is an example where the JOIN clause allows you to remove the
WHERE clause to filter rows. Usually it is better to use a JOIN to
filter rather than a WHERE statement but, in this case, both queries
are at the same performance level.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
