Hi again!

At 12:28 10.02.2003 +0200, you wrote:

>Hi Set,
>
><quote>
>No, you are very wrong. If this was the case, then Fb would have to use
>Natural in its plans a whole lot more.
></quote>
>
>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
>plans....

What I tried to say was simply that if you select without any where clause
(i.e. any criteria that is not joining tables - regardless of where it is
specified), then at least one table have to be accessed in its natural
order. Joining tables first and thereafter take into account the where
criteria (again, regardless of where it is specified) would be inefficient,
and I do not believe Fb would done anything that way.

><quote>
>There does exist a third method as well putting everything in the where
>clause
></quote>
>
>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 ????"

Well, it is just the standard way in SQL-89, which was used before the JOIN
clause came with SQL-92. It is still possible to do WHERE
table2.FK_ID=table1.PK_ID and not have any JOIN clause at all, but it is
discouraged to do things that way.

>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
>following
>
>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
>set
>
>Whereas,
>
>Right Outer Join Method 2:
>Select blah, blah
> From Table1
>Right outer join Table2 on (Table1.PK_ID = 5 and Table2.FK_ID =
>Table1.PK_ID)
>
>This results in all the records in Table1 where PK_ID = 5, and a full result
>set of Table2

Yes, the only way to limit records to the right of a left join or to the
left of a right join is in the join itself. I consider specifying such
criteria in the where clause to be a programming mistake since it is
difficult to understand what the programmer actually meant. My initial
answer about preferring to put things in the where clause is only valid for
inner joins, to the left of a left join or to the right of a right join.
But I doubt it is any slower, or have you measured this to be the case?

HTH,

Set

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