Subqueries in sql server

Question

0.00/5 (No votes)

See more:

Hi friends,

Could you please tell me the disadvantages of subqueries in sql server?

I have gone through google.

But, I got only one answer that subqueries execution is slow than joins because of query optimizer process in sql server.

Please tell me if there are other disadvantages also...

Posted 21-Jul-11 10:32am

Member 7920084

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DominicZA · Answer 1 · 2011-07-21T11:13:00

The best way to see whats going on is to use SQL to give you an execution plan. Here are the basics though.

1)JOIN:

SQL looks at the query, and basically creates a new table of the joined tables and then gets the results from there. So ultimately, its just one call!

2) Sub query:

SQL now has to run multiple queries to get your result, which, although might be instant because SQL is made by magical fairies, is still going to be a lot more resource intensive.

The best way for you to see the disadvantages is to run a query with an execution plan and see the results! That way, you can see for yourself!

walterhevedeich · Answer 2 · 2011-07-21T15:20:00

Solution 2

In addition to the previous solution, you can use database performance monitoring tools such as SQL Profiler[^], if you are concerned with number figures in terms of their difference in execution.

Posted 21-Jul-11 15:20pm

walterhevedeich

thatraja · Answer 3 · 2011-07-21T19:36:00

Solution 3

I prefer EXISTS for this kind of situation.
SQL Server: JOIN vs IN vs EXISTS - the logical difference[^]
IN vs. JOIN vs. EXISTS[^]

Posted 21-Jul-11 19:36pm

thatraja

v2