Link to home
Start Free TrialLog in
Avatar of mcmonap
mcmonapFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Derived tables and subqueries

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.
ASKER CERTIFIED SOLUTION
Avatar of DireOrbAnt
DireOrbAnt

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DireOrbAnt
DireOrbAnt

And as far as:
"..a subquery is quite different. If anything a derived table is closer to a correlated subquery, than a subquery."
I don't think I agree... Well first, one is a table, the other a subquery... But if your derived table doesn't join or use WHERE that tie into the outside query, then it's not correlated much ;)
Avatar of Anthony Perkins
>>But if your derived table doesn't join or use WHERE that tie into the outside query, then it's not correlated much ;)<<
I agree.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
acperkins has enough points in this TA, so I don't think he needs anymore...

I'm just kidding and I dont' object at all :)
That was good, it made my day.  I though I was the only one that cracked jokes here.
You got some competition here now... What's the point of being serious anyway?
Avatar of mcmonap

ASKER

Sorry - very bad form, I went away to have a think and a look around and then promptly forgot about the question!

Based on the comments and a bit more research (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt - this _is_ _not_ a riveting read!) it looks to me like a subquery can be considered to be a query in a query.  These can then be divided into differenet types - scalar, derived table and correlated.  These types are then more specific in definition and can be used as ac says above although additionally it is possible to have correlated subqueries in the select part of a query so long as they are scalar in nature, and you could have a scalar subquery in the WHERE clause without it needing to be correlated.
Actually when I saw the comment from DireOrbAnt  I checked again if it is not acperkins :) Because he is the one that usually makes me smile when I make cleanup around :)

mcmonap,

Thanks for coming back and closing the question :)