||SQL Server Tips by Gama and Naughter
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
(SELECT (min(royalty)+max(royalty))/2 as avgTotal FROM dbo.roysched
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:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter