|
Hi! I have a database with one table for Descriptions in different languages, with LanguageID, TableName, ForeignID and Description. When I need to join this Descriptions table, I naturally join on TableName.ID = ForeignID. But where do you recommend putting the condition for TableName and LanguageID? Like this? Or like this?
(comments are locked)
|
|
+1 for WilliamD, but I would add that it's probably better to keep them in the joins because it makes it simpler to modify the query later on. For example, if you want to turn it into a subquery - it's not a big deal. If you want to turn it into a LEFT OUTER join to see which rows don't have corresponding data, it's just a change of the join (whereas there the WHERE clause version actually has a different meaning)... Thanks Matt. I have always had these conditions in the join conditions, but for some reason I started wondering if there's a "best practice". You've at least proven to me that geography does matter in this case.
Dec 21 '10 at 12:07 AM
Magnus Ahlkvist
No worries. Glad you commented, because I forgot to +1 your question! :)
Dec 21 '10 at 12:45 AM
Matt Whitfield ♦♦
+1 Matt - didn't think about the easier redesign part.
Dec 21 '10 at 12:58 AM
WilliamD
I hate to be the party pooper, but I'm still with @WilliamD on this. I'd say that best practice dictates that filtering information go into the WHERE clause and only JOIN information go into the JOIN area. Can you do it differently? Sure. But I think it can lead to confusion or bad conclusions. Just my .02 cents.
Dec 21 '10 at 05:43 AM
Grant Fritchey ♦♦
(comments are locked)
|
|
As far as I am aware, both of these will result in the same execution plan. I think I prefer the first example, simply because the join conditions are kept together with the joins themselves. The second example makes me look at the joins, then the where clause, then back at the joins to see what the aliases are. I think it is a thing of preference only. My 2 cents :o)
(comments are locked)
|

