Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vaibhavpingle
Starting Member
28 Posts |
 Posted - 2007-06-12 : 12:20:26
|
| Please let me know this......i was asked this question during my evaluations at my office.When do you use and when do u use a subquery.....?please lt me kow the ideal condition for this.I was asked to answer a specific reason for the above question.thank you....there is one more interesting question coming up....Trust in Technology mate .....'Not in Human Beings' |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-12 : 12:26:54
|
| Use the one that is more efficient or easier to understand depending on which is more important.SQL Server is much better at optimising subqueries into the main query than it used to be so it often doesn't matter now.Also a subquery can be faster than a join (oddly enough) and you can't tell without testing - and it may change due to the resources available.Often where x in (select ...)is faster than the joinSo the question doesn't really have an answer so it's a good interview question.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-06-12 : 12:34:32
|
| I will try to avoid subquery if is returning considerable number of records. For small number of records, I don't mind subquery or Join.Also where you need to use correlated subqueries, there is really no option but to use subquery.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-13 : 09:56:55
|
| I used to as well but with 2005 it can be faster to use a subquery to return many millions of rows.I've given up trying to predict the optimiser - oh for the days of v6.5.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-06-13 : 10:01:40
|
| nr,Have you time-tested Subquery vs. Join approach in SQL 2005? It is surprising to know that Subquery is actually faster even for larger number of rows.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-13 : 10:07:13
|
| I've only optimised queries - I don't think it would be worthwhile to try to get a general recomendation.I usually go with what is more easily understood/maintained and only change it if there are performance issues.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Join and subquery