Where to put conditionals in ANSI-syntax SQL queries

"Is 1) returning the product of the join and only then filtering out those joined records which have weight >= 500? And is 2) filtering out those prior to the join?"

true, except that logically, 2) is applying the filter as part of the join, not prior to the the join. For an inner join, however, this distinction will have no effect on the final resultset of the query. For Outer joins, otoh, this distinction can alter the results.

NOTE: Using the word logically (saying that this is the sequence in which the query processor logically performs these steps) is important, and is meant literally. The actual sequence used by the proccessor may or may not conform to this logical description. It is only guaranteeed that the results of the query will appear as though the processor performed these steps in this order...

The output is the same since it is an inner join, and the query plan should be the same. I prefer the syntax in #1 as it separates the joining clause from the filter clause, and is thus more readable to me. This makes it easier to double-check your ON clauses when reading over a large query, as it reduces the noise that would be there if filter clauses were mixed in.

When doing outer joins, this is no longer a matter of preference - where you put BAR.WEIGHT < 500 would have a direct effect on the output.

Where it makes a difference is when you have OUTER JOINS, then you will get different results from the queries.

See http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN for explanation as to why.