Subject RE: [ib-support] Correct method for joining tables
Author Stephen Wood
Hi Set,

No, you are very wrong. If this was the case, then Fb would have to use
Natural in its plans a whole lot more.

I don't understand what you mean, if you specified a where clause using an
indexed field, would it matter if it was in the "where" clause section or in
the "on" section of the join? If not, then it wouldn't need to do natural

There does exist a third method as well putting everything in the where

I don't understand this, if you put everything in the "where" clause, what
would you put in the "on" clause on the join?, for example, "inner join
Table2 on ????"

Also, there is another twist in my example, what about Right outer
joins....they don't work properly if you use the "where" clause, as in the

Right Outer Join Method 1:
Select blah, blah
From Table1
Right outer join Table2 on (Table2.FK_ID = Table1.PK_ID)
Where Table1.PK_ID = 5

This results in a partial return of Table2's records, even though you have
done a Right outer join, because the where clause acts on the entire result
set (which is why I say it's slower because the entire result set is
generated, and then the where clause creates a subset for the actual result


Right Outer Join Method 2:
Select blah, blah
From Table1
Right outer join Table2 on (Table1.PK_ID = 5 and Table2.FK_ID =

This results in all the records in Table1 where PK_ID = 5, and a full result
set of Table2

I hope my explanations are not confusing everyone...

Thanks again

[Non-text portions of this message have been removed]