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
>

Booleans in the where clause that restrict the join operation are
handled with the join...

select a.f1, b.f1 from a join b where a.f1 = b.f2

should be just as fast as

select a.f1, b.f1 from a join b on a.f1=b.f2


The place where the choice of putting a boolean in the ON clause
rather than the WHERE clause matters is when you're doing an outer
join.

This statement is equivalent to an inner join.

select a.f1, b.f1 from a left join b where a.f1 = b.f2

This will fine all the records from a and any matching records from b

select a.f1, b.f1 from a left join b on a.f1 = b.f2


It's safe to distribute booleans in inner joins but not outer.

Regards,


Ann