Subquery vs Derived Table

Question

Subquery vs Derived Table

Christopher Stobbs

SSC-Insane

Points: 21098
More actions
July 11, 2008 at 10:56 am

#195201

Hi all,
OK, I should know this to be honest, but after reading up on the web on loads of different sides loads of people are getting the two of these definitions confused which in turn has confused me! 🙁
Does anyone have a good definition of the difference between the two with an example of each...
I always thought that a Subquery in a join is called a derived table?
subqueries in Selects or where clauses are subqueries?
please someone take away the pain :w00t:
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
SQL-4-Life

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 1

According to Itzik Ben-Gan, a subquery is any of the scalar, multivalued or table expression queries written into an outer queries. He then goes on to refer to the scalar and multivalued queries as subqueries and tables as table expressions (derived tables).

I like his definition of subquery, but I like derived table better. I guess, to a large extent, the terms are interchangeable to a degree.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 2

To me, a sub-query is dependent on the containing query. A derived table (table expression) is independent of the containing query. That's why the example below is invalid (the derived table B references table A).

select A.*

from A

cross join

( select val1 from B where B.val2 = A.val2 ) as B

GSquared SSC Guru Points: 260824 More actions · Answer 3

"Sub-query" simply means a query inside another query. Thus, it's an embracive term. A derived table is a type of sub-query, as is an inline query.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 4

... or correlated subquery.

Spot on, Gus. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 5

Shouldn't the following already be a derived table?

SELECT SomeIntCol - 100 FROM dbo.BaseTable

Obviously it is not a subquery.

Best Regards,

Chris Büttner

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 6

Yeah, if you want to equate data set or result set with derived table, then they're the same. Generally though, most people refer to a derived table as a subquery that returns data acting as a table within a greater query.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning