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 |
|
doco
Yak Posting Veteran
77 Posts |
 Posted - 2008-09-28 : 20:30:12
|
| Is there an advantage to placing criteria in the ON clause over the WHERE? If so, what?Education is what you have after you've forgotten everything you learned in school |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-09-28 : 22:23:35
|
| There certainly is an advantage when you're doing outer joins - it's the only way to get it to work if your join condition is more than equality.Presumably though you are talking performance benefit. If so, just give it a go and look at the query plan, but I suspect it will produce the same plan so long as the two queries are semantically the same (i.e. inner join).My personal opinion is don't sweat this kind of small stuff. It almost never makes a difference and any reports of differences are usually baseless guesses, urban myths or badly performed tests. |
 |
|
|
doco
Yak Posting Veteran
77 Posts |
Posted - 2008-09-29 : 00:40:30
|
| Thanks. Wasn't thinking in terms of performance. Our DBA was suggesting placing certain criteria in the ON clause and in this particular case was using LEFT OUTER JOIN. He couldn't explain why - just 'knows by experience' some records would 'fall out' due to a null field, etc. if stated criteria was placed in the WHERE clause.Education is what you have after you've forgotten everything you learned in school |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-29 : 00:43:48
|
quote:
Originally posted by doco
Thanks. Wasn't thinking in terms of performance. Our DBA was suggesting placing certain criteria in the ON clause and in this particular case was using LEFT OUTER JOIN. He couldn't explain why - just 'knows by experience' some records would 'fall out' due to a null field, etc. if stated criteria was placed in the WHERE clause.Education is what you have after you've forgotten everything you learned in school
this article explains it very wellhttp://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/05/14/60205.aspx |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-09-29 : 04:20:53
|
quote:
Originally posted by doco
Our DBA was suggesting placing certain criteria in the ON clause and in this particular case was using LEFT OUTER JOIN. He couldn't explain why - just 'knows by experience' some records would 'fall out' due to a null field, etc. if stated criteria was placed in the WHERE clause.
Sounds like you need to have a word with your DBA. This is schoolboy stuff. Everyone who develops using a database should know this after a short learning process. A DBA worthy of the role should know how the things work, not be guided by some mysical feeling in his waters. Either that or send him on a communication course. |
|
|
|
doco
Yak Posting Veteran
77 Posts |
Posted - 2008-09-29 : 06:26:41
|
Thanks visakh.quote:
Sounds like you need to have a word with your DBA. This is schoolboy stuff. Everyone who develops using a database should know this after a short learning process. A DBA worthy of the role should know how the things work, not be guided by some mysical feeling in his waters. Either that or send him on a communication course.
He really is quite good. It is most likely that I am the one misquoting or misunderstanding him in my quest to learn new tricks at an advanced age :)Thanks to allEducation is what you have after you've forgotten everything you learned in school |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-29 : 06:36:18
|
quote:
Originally posted by doco
Thanks visakh.quote:
Sounds like you need to have a word with your DBA. This is schoolboy stuff. Everyone who develops using a database should know this after a short learning process. A DBA worthy of the role should know how the things work, not be guided by some mysical feeling in his waters. Either that or send him on a communication course.
He really is quite good. It is most likely that I am the one misquoting or misunderstanding him in my quest to learn new tricks at an advanced age :)Thanks to allEducation is what you have after you've forgotten everything you learned in school
welcome  |
|
|
|
|
|
|
ON vs WHERE