troubleshooting Question

Derived tables and subqueries

Avatar of mcmonap
mcmonapFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL Server
9 Comments2 Solutions1810 ViewsLast Modified:
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.
Join our community to see this answer!
Unlock 2 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros