| Author |
Topic |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
 Posted - 2007-07-30 : 19:49:21
|
| Hi,I was having a conversation with another developer about the order that the join conditions are placed. More specifically, about the order the tables are referenced in the join condition. There are two ways to do it, for example:[CODE]-- Here the table that is referenced first was "declared" first.SELECT *FROM Logon LEFT OUTER JOIN Thread ON Logon.LogonID = Thread.LogonIDLEFT OUTER JOIN Message ON Thread.ThreadID = Message.ThreadID-- Here the table that is referenced first is the table being joined directly above it.SELECT *FROM Logon LEFT OUTER JOIN Thread ON Thread.LogonID = Logon.LogonIDLEFT OUTER JOIN Message ON Message.ThreadID = Thread.ThreadID [/CODE]I realize this is not that big of a deal, but I was wondering if anyone had a good/valid reason for doing one versus the other.Just curious,-Ryan |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-30 : 21:48:50
|
| I dont think the order matters.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-31 : 02:19:01
|
I prefer the second alternative, because I think it is easier to read and maintain.This way, I can see which table that is JOINed very fast, and also at the end I can see how it is bound.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-31 : 08:00:54
|
| I use the second too though I think of many of the conventions you might use this is one of the less important. I would be happy working with someone else's code who wrote them as the first example if they were consistent. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-07-31 : 11:34:22
|
| Thanks for the feedback. :)-Ryan |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-31 : 11:51:55
|
| Well the question is posted in a MICROSOFT SQL SERVER forums. So it doesnt matter what it means in other databases.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-31 : 11:53:25
|
quote:
Originally posted by X002548
It matters if you are using DB2 believe it or not
Can you elaborate that?What can you expect, when you switch the two sides of the equal sign?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-07-31 : 12:57:27
|
| Well actually I meant the order of the tables...saw that behaviour about 6 months ago when the EXPLAIN was scanning...change the table order change the plan...>> Well the question is posted in a MICROSOFT SQL SERVER forums. So it doesnt matter what it means in other databases.Really? I'm kinda new here...Thanks for the adviceBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2007-08-03 : 16:11:36
|
quote:
Really? I'm kinda new here...Thanks for the advice
Yes, really! Come on, Brett! I mean, according to your ID block you have < 2 full posts here (1.0257). Oh, wait. Whatdya mean, there's no decimal point? Really? You mean that's TEN THOUSAND, TWO HUNDRED FIFTY SEVEN posts? Oops! My bad.  |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-08-04 : 06:26:29
|
| I thought it matters if which table you are referencing with since it's a left outer joinbut... I'm not a guru --------------------keeping it simple... |
|
|
|
|
Opinion/Standard for Join Condition