At 03:04 PM 26/06/2003 +0200, you wrote:

>Ah, my turn to ask a question, since this is something partially relevant
>for us. We have a situation where for some older records we do not have a
>link and has set the value to 0 for such records. It would be something like
>
> >select t1.pk1, t1.pk2, t1.col1, t1.col2, t2.cola, t2.colb
> >from atable t1
> >join btable t2
> >on t2.fk1 = t1.pk1
> >and (t2.fk2 = t1.pk2 or t2.fk2 = 0)
>
>(and then of course a where clause, I've no intention to return 2 million
>records)
>
>I'm under the impression that it is better to have where criteria in the
>JOIN part than join criteria in the WHERE clause and since this is an or
>between something that partly is a join and partly is a where, I've simply
>guessed that we better put it in the join clause (though I haven't been
>100% consistent). What is the correct answer for such a question, or am I
>simply free to choose between two wrong solutions?

You're free to do whatever you like! :-)

If you have a foreign key on those two btable FK columns, then you couldn't
have a row in btable with fk1 = something and fk2=0 unless there was a
matching primary key in atable. IOW, your "OR" test is redundant if those
two cols are defined as a foreign key and it would probably stuff up the
optimizer as well.

If they are not defined as a foreign key then the question of splitting the
key index between the JOIN criteria and the search criteria doesn't
arise. If you needed to make that join, it would be fairly
absurd. Still...in that case you'd need to have separate indexes on them
experiment with plans to see what worked better - each case on its merits
and a to-do list item to fix up that relationship! :-))