Solved: Derived tables and subqueries

Create Account

Microsoft SQL Server

Questions

Followers

Top Experts

mcmonap🇬🇧

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.

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

ASKER CERTIFIED SOLUTION

DireOrbAnt

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

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🇺🇸

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

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

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

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 a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

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

Microsoft SQL Server

Questions

Followers

Top Experts

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.