Joins - best practices? 1

[bowline]

I have two quick questions on the best practices with joins.

First question - When joining multiple tables for a query (all with one field in common):

1. Do you join all of the tables through one central table? I usually follow this route, using the one that will result in the biggest set of data as this central table. But I have always done this only for clarity purposes.
Ex:
from Table1 t1 inner join Table2 t2 on (t1.IdField = t2.IdField)
inner join Table3 on (t1.IdField = t3.IdField)
where t1.DataField = 'XXX'

2. Or go with linking sequentially.
Ex:
from Table1 t1 inner join Table2 t2 on (t1.IdField = t2.IdField)
inner join Table3 on (t2.IdField = t3.IdField)
where t1.DataField = 'XXX'
________________________

Second question - does it matter where the criteria lies?
Ex:
from Table1 t1 inner join Table2 t2 on (t1.IdField = t2.IdField)
inner join Table3 on (t2.IdField = t3.IdField)
where t1.DataField = 'XXX'
or
Ex:
from Table1 t1 inner join Table2 t2 on (t1.IdField = t2.IdField and t1.DataField = 'XXX')
inner join Table3 on (ti.IdField = t3.IdField)
_________________________

I have my own methods that I follow, more out of habit than understanding, and was just curious why.
Thanks for any insight!

 

[MDXer]

SemoerFiDownUnda has probably the best FAQ on this issue I have seen.

JOIN Fundamentals faq183-4785

He really explains things in a manner to make it understandable.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[bowline]

Thanks - he suggests using the base table for the joins. And he mentions that you can put the "where clause" into the "on" part of the join - but not really why on either one.

I was not sure if anyone could point me into the direction of those "why's".

He did confirm that I was doing it the "right way" though.
Thanks for the article. : )

 

[MDXer]

THe reason to place the where criteria on the ON part of the join is preformance based. In my experience I have learned, if you do a

Select
a.Col1
a.Col2
b.Col5
from TableA a
     JOIN TableB b On b.Col3 = a.Col3
where b.Col4 = 'XXX'

then look at the query execution plan for the estimated rows for each of your joins you will see that all records from b.col4 will be returned to the HASH and then the where will be applied. However if you do

Select
a.Col1
a.Col2
b.Col5
from TableA a
     JOIN TableB b On b.Col3 = a.Col3
                   and b.Col4 = 'XXX'

if Col4 contains only 5 rows that have XXX the number of rows returned to the Hash portion of the query will only be 5.

In my opnion it is much easier to read and understand what a piece of code is doing when you start with the base and then add to it. Also it makes using and creating OUTER joins much more intuitive. If your the only person who will ever see your code then it probably doesn't matter, but if someone else one day will inherit this code then you may want to make the effort. We have all inherited code that is a pain to understand wha the person is trying to do, and it is even worse when the outcome isn't really what was expected.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[bowline]

Excellent - thank you very much for the explanation.