Tom Conlon wrote:
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')
- FROM clause (includes JOIN + ON statements)
- WHERE clause (select expressions not referencible yet)
- GROUP BY clause
- HAVING clause
- SELECT clause
- 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.
Ann W. Harrison answers:
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.
Ivan Prenosil adds:
It is misinterpretation. SQL standard does not tell what the execution order should be, but that the result should be as if the clauses were executed in specific order. Each engine is totally free to implement whatever order thinks is the best for it, as far as the returned result is as expected.