Where can we use SUB QUERY and JOIN?

Question

Post reply

Where can we use SUB QUERY and JOIN?

Britto

SSC Enthusiast

Points: 153
More actions
December 7, 2009 at 6:13 am

#143429

Hi,
I am having a doubt regarding the appropriate use of SUB QUERY and JOIN.
Please tell me the usage of these techniques, exact place in SQL query which will give optimized performance.
Regards,
Britto

Viewing 9 posts - 1 through 9 (of 9 total)

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

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

Have you tried Google?

Let me suggest you try Google, read the examples that it finds and apply that T-SQL commands on your database.

When you have important issues, definitely this is the place to visit, but not when learning SQL Server.

W3Schools is the place you can visit t learn these.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Britto (12/7/2009)
Hi,
I am having a doubt regarding the appropriate use of SUB QUERY and JOIN.
Please tell me the usage of these techniques, exact place in SQL query which will give optimized performance.
Regards,
Britto

This is similar to expressing doubt regarding the appropriate use of wheels and wings on an aircraft!

Do you have a real-life example which you are attempting to solve?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

Chris Morris-439714 (12/7/2009)
This is similar to expressing doubt regarding the appropriate use of wheels and wings on an aircraft!

😀 You made my day -- best part is, you are correct!

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Britto SSC Enthusiast Points: 153 More actions · Answer 4

Britto

SSC Enthusiast

Points: 153

December 7, 2009 at 10:11 pm

#1089013

Thank you so much for your information.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

As a matter of preference as well as having seen substantial performance gains, I avoid the use of subqueries and use Join statements as well as CTE's. Moving subqueries into ctes, makes it easier to read and has drastically improved performance in most scenarios I have encountered.

Unfortunately, in SQL 2000 there often were few options to replace the subquery with something more efficient.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Britto SSC Enthusiast Points: 153 More actions · Answer 6

Hi Chris,

I am not an expert on Performance tuning. We are using so many views with JOINs in many places, irrespective of table size.

Application is too slow, even though just few records presented in the table.

Which technique is best to use when performing operations with huge table size and vice versa?

Thanks & Regards,

Britto

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

Britto (12/7/2009)
Which technique is best to use when performing operations with huge table size and vice versa?

It depends.

There's no one way that's always right, no one way that's always wrong. Try both, see which is faster.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Britto (12/7/2009)
Hi Chris,
I am not an expert on Performance tuning. We are using so many views with JOINs in many places, irrespective of table size.
Application is too slow, even though just few records presented in the table.
Which technique is best to use when performing operations with huge table size and vice versa?
Thanks & Regards,
Britto

As Gail says, it depends.

Post a couple of samples, queries which you know are hitting performance. It would help to post the execution plans also.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden