> > 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.

Set, as far as I know, you can do these joins as well:

ON (mycol < othercol)

or

ON (myudf(col) = myudf(othercol))

But I must admit I haven't tried ;)

> > > 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

Right, there's a difference, I agree, between indexed comparisons
and non-indexed ones.

Now, I also seem to remember a Fb 1.5 configuration setting
(looking it up)...


# ----------------------------
# Boolean evaluation method (complete or shortcut)
#
# If your SQL code depends on side-effects of full evaluation of OR
# and AND statements (right-hand-side terms), even if the expressions
# final result could be determined by just examining the value of the
# first term, you might need to turn this on.
#
# Type: boolean
#
#CompleteBooleanEvaluation = 0

I wonder if this applies to JOIN conditions as well. Arno?

> 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.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com