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!
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!