What's the difference between these two queries? I've been resistant to jumping on the ANSI-syntax bandwagon because I have not been able to unravel various syntactical ambiguities.
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?
Is 2 bad syntax? Why might I use that?
1:
SELECT SOMETHING
FROM FOO
INNER JOIN BAR
ON FOO.NAME = BAR.NAME
WHERE BAR.WEIGHT < 500
2:
SELECT SOMETHING
FROM FOO
INNER JOIN BAR
ON FOO.NAME = BAR.NAME AND BAR.WEIGHT < 500
"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...