--- In firebird-support@yahoogroups.com, "Bamsemums Tonies" wrote:

> Rule of thumb: join conditions in the ON clause, filtering
> conditions (for the FINAL resultset, that is, AFTER doing the JOIN)
> go in the WHERE clause.

Agreed, though with an inner join (in outer joins it makes a
difference whether things are in the JOIN or WHERE clause, so you
cannot choose that freely) I'm uncertain where I'd put UDF calls that
compared joined tables.

> > And the other question remains: does FB stop evaluating the ON or
> > WHERE when the first condition evaluates to FALSE and all other
> > conditions are ANDed with the first? If so, it would make sense
> > to put a condition that often likely evaluates to False at the
> > beginning of the expression, wouldn't it?
>
> I believe it evaluates everything, but I might be wrong here ...
>
> Gotta wait on a definitive answer on that one.

I'm definitely not a definite answer to this one, though my hunch is
not quite the same as Martijns. If an indexed comparison evaluates to
false, I don't think non-indexed comparisons are evaluated. Though
considering Ann's common statement that indexes (or conditions - I
don't quite remember) are bitmapped together, I don't think they are
individually considered one by one either. In general, I just trust
the optimizer to figure out how to give me the result set as quick as
possible, and I never care about the order in which I write my
conditions (well, I do care, but not for speed reasons). Then, if the
optimizer doesn't get the optimal plan, I start to interfere by
preventing it from using certain indexes. I've never seen that
changing the order of conditions in ON or WHERE clauses change the
plan in any way in queries that don't involve any OUTER JOINs, and
would be shocked if it did.

Set