x

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
more ▼

asked Dec 20, 2010 at 11:49 PM in Default

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.5k 16 19 33

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
+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)...
more ▼

answered Dec 21, 2010 at 12:04 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

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, 2010 at 12:07 AM Magnus Ahlkvist
No worries. Glad you commented, because I forgot to +1 your question! :)
Dec 21, 2010 at 12:45 AM Matt Whitfield ♦♦
+1 Matt - didn't think about the easier redesign part.
Dec 21, 2010 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, 2010 at 05:43 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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)
more ▼

answered Dec 20, 2010 at 11:59 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x116

asked: Dec 20, 2010 at 11:49 PM

Seen: 1087 times

Last Updated: Dec 20, 2010 at 11:49 PM