I understand a derived table to be the result of a subquery that returns a table (as opposed to a scalar value or a row)
ie. This subquery aliased as a creates a derived table from which reults are selected
SELECT a.* FROM (SELECT TOP 10 * FROM TableA) a
Recently I have been told that "..a subquery is quite different. If anything a derived table is closer to a correlated subquery, than a subquery."
Now I understand a correlated subquery to be:
ie. So the subquery must be evaluated for each row of the outer query
SELECT * FROM TableA a WHERE a.col1 > (SELECT col1 FROM TableB b WHERE a.ID = b.ID)
I'd appreciate anyones comments on the definitions since I'd rather get it right if I'm not...
Points spread based on content of answer and/or links to relevant info - Thanks folks.