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 |
|
y0zh
Yak Posting Veteran
60 Posts |
 Posted - 2010-04-22 : 10:30:44
|
| Could you explain where should we use join instead of using sub select? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-22 : 10:45:20
|
I think there is no general answer because it depends...I would say:Whenever you have typed in a sub select because it was faster not to think about how to join this in the right way then you should think about changing that to a join. 
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
y0zh
Yak Posting Veteran
60 Posts |
Posted - 2010-04-22 : 10:48:17
|
| Would be I right if I say that with joing you won't select duplicated rows? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-22 : 10:50:32
|
| nope. not right. if relation is 1-m or m-m you'll get duplicated rows with respect to a table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-22 : 10:58:29
|
| think of sub select as a LEFT OUTER JOIN. You can rewrite most (all?) sub selects as a left outer join.Edit: -- The join will most often be more performantCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-22 : 11:04:03
|
quote:
Originally posted by Transact Charlie
think of sub select as a LEFT OUTER JOIN. You can rewrite most (all?) sub selects as a left outer join.Edit: -- The join will most often be more performantCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
But i've seen cases where subquerries perform better. especially cases like EXISTS() check------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-22 : 11:45:18
|
quote:
Originally posted by visakh16But i've seen cases where subquerries perform better. especially cases like EXISTS() check
Indeed -- not that I said 'most often'SQL seems to be like this -- there are exceptions to everything.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-23 : 02:41:41
|
| yeah...thats true------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Join and sub select