asked
JOIN condition or WHERE clause
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? SELECT c.ChapterID, cd.Description as ChapterName, s.SectionID, sd.Description as SectionName FROM tblChapter c INNER JOIN tblSection s on c.ChapterID = s.ChapterID INNER JOIN tblDescription cd ON c.ChapterID = cd.ForeignID AND cd.TableName='tblChapter' and cd.LanguageID=1 INNER JOIN tblDescription sd ON s.SectioNID = sd.ForeignID and sd.TableName='tblSection' and sd.LanguageID=1 Or like this? SELECT c.ChapterID, cd.Description as ChapterName, s.SectionID, sd.Description as SectionName FROM tblChapter c INNER JOIN tblSection s on c.ChapterID = s.ChapterID INNER JOIN tblDescription cd ON c.ChapterID = cd.ForeignID INNER JOIN tblDescription sd ON s.SectioNID = sd.ForeignID WHERE cd.TableName='tblChapter' and cd.LanguageID=1 and sd.TableName='tblSection' and sd.LanguageID=1
+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.
No worries. Glad you commented, because I forgot to +1 your question! :)
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.
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)
