Avatar of mcmonap
mcmonap
Flag 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.
Microsoft SQL Server

Avatar of undefined
Last Comment
Venabili

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
DireOrbAnt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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 ;)
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
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
DireOrbAnt

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 :)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Anthony Perkins

That was good, it made my day.  I though I was the only one that cracked jokes here.
DireOrbAnt

You got some competition here now... What's the point of being serious anyway?
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Venabili

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 :)