Hi Stephen!

At 11:23 10.02.2003 +0200, Stephen Wood wrote:

>Hi guys 'n gals,
>
>I've always wondered what the correct procedure is for joining tables using
>the inner join or left/right outer joins....
>
>Method 1:
>
>Select blah, blah, blah
> From Table1
>Inner join Table2 on (Table1.PK_ID = 5 and Table2.FK_ID = Table1.PK_ID)
>
>Method 2:
>Select blah, blah, blah
> From Table1
>Inner join Table2 on (Table2.FK_ID = Table1.PK_ID)
>Where Table1.PK_ID = 5
>
>My logic says that Method 1 is the correct way because you are limiting the
>result set at the table joins, instead of Method 2 where you are joining all
>records from both tables, and then the Where clauses is creating a subset of
>the total result set....
>
>Am I correct in my thinking, or does the database engine optimize the query
>regardless of the method you use?

No, you are very wrong. If this was the case, then Fb would have to use
Natural in its plans a whole lot more. The optimizer tries to find the
least expensive method of retrieving the records (admittedly, it doesn't
always succeed), and in this simple case that would be to first find those
records having Table1.PK_ID = 5 and THEN join Table2.

So, generally I prefer method 2. Not because it is any faster than method
1, but simply because it is easier to read (separating join from where).
There does exist a third method as well putting everything in the where
clause, but that one has several disadvantages, one of them being that it
makes things more difficult for those of us who use IBO.

HTH,
Set

- I support Firebird, I am a FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation