Re: Subquery in the FROM clause

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

klem s wrote:
So essentially data returned from subquery ( inside FROM clause ) acts
as a table?


Yes, this is why the derived table is called a table expression.


Also, as far as I can tell we can’t have a correlated subquery inside
a FROM clause? Why is that?


On SQL Server 2005/2008 you can use the APPLY operator in the FROM clause which allows you to use a correlated subquery as data source.


Another rule is that ORDER BY cannot be used unless the TOP option is specified.

May I ask why that is?

Because a table is unordered set. Since the derived table is a table expression, therefore it cannot be ordered set and ORDER BY can be used only with the TOP option because then it serves the purpose of providing order for the top rows to be returned. Even then it does not guarantee the result rows will have any order.

--
Plamen Ratchev
http://www.SQLStudio.com
.