On Tue, May 27, 2014 at 11:17 AM, jakefeed@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

We have some new members of our team with a MS SQL background, and they have a preference for including filter conditions in the JOIN clause.  They say, "the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join" and "It has to do with the record set that is available when the WHERE clause executes. When you move more conditions into the ON clause, you have less records (potentially) when the WHERE clause executes"  Is that true?  

Not for Firebird, and probably not for MS SQL either.  For inner joins, the optimizer picks the join order that minimizes the number of rows to be retrieved based on all conditions the JOIN ON terms and the WHERE terms.  Before doing so, Firebird distributes equalities, so if you have a multi-way join on a single term (e.g. customer.cust_id = invoice.cust_id for the first pair and invoice.cust_id = payment.cust_id on the second) Firebird can join any of the pairs.  With outer joins, a condition in the WHERE clause that affects the right side of a left outer join (the one that doesn't have to exist) effectively turns off the outerness of the join (unless it includes OR <value> IS NULL).   

It's a pretty pathetic join optimizer that doesn't move conditions around and depends on the user's syntax to order conditions.   Logically, you can think about the operation as doing the joins in the order presented on the conditions in the JOIN ... ON, then applying the WHERE conditions to that output stream, but one of the nice things about relational databases is that the engine is free to do anything behind the scenes to make your query fast in spite of your efforts.

Good luck,

Ann