I seriously doubt Firebird is anything like ColdFusion in this area,
Tom. My experience tells me that Firebird mixes several of these steps
when preparing a statement(*), and I think I would have known by now if
such considerations were applicable to Firebird when it comes to whether
things should be put in JOIN or WHERE.

What I have heard, is that you should keep things logically being a JOIN
in the JOIN clause and other stuff in the WHERE clause. The only
practical importance I've heard about, is that IBO cannot (or at least
couldn't) determine the KeyLinks automatically if things were joined in
the WHERE clause.

OUTER JOINs are different, whether you put things in the ON or WHERE
clause may influence how many rows are returned (not matching rows being
the difference).

HTH,
Set

(*)
I'm pretty certain that the Firebird optimizer can take

SELECT *
FROM TableA A
JOIN TableB B on A.Field1 = B.Field1
WHERE B.Field1 = 1

and decide to start by using the index for TableA.Field1 - something
that means it knows that it can choose whether to compare 'B.Field1 = 1'
or 'A.Field1 = 1' since these two fields should be equal. I cannot see
how Firebird could do this if your order of execution should be strictly
followed. Moreover, in addition to FROM and WHERE, both GROUP BY and
ORDER BY can affect the chosen plan.

Tom Conlon wrote:

> Hi,
>
> Can anyone confirm if FB follows this SQL order of execution:
> (see http://www.bennadel.com/blog/70-SQL-Query-Order-of-Operations.htm
> and Celko 'SQL for Smarties')
>
> 1. FROM clause (***includes JOIN + ON statements***)
> 2. WHERE clause (select expressions not referencible yet)
> 3. GROUP BY clause
> 4. HAVING clause
> 5. SELECT clause
> 6. ORDER BY clause
>
> This seems to have very significant implications for a complex query
> that has joins - whether to _consciously_ put conditions as part of
> the ON statement rather than the WHERE statement.
>
> From bennadel:
> "...Since this clause executes first, it is our first opportunity to
> narrow down possible record set sizes. This is why I put as many of my
> ON rules (for joins) as possible in this area as opposed to in the
> WHERE clause:
>
> e.g.
> FROM contact c
> INNER JOIN display_status d ON (c.display_status_id = d.id AND
> d.is_active = 1 AND d.is_viewable = 1)
>
> This way, by the time we get to the WHERE clause, we will have already
> excluded rows where is_active and is_viewable do not equal 1"
>
> Tom