SQL Query JOIN Performance

Question

I wanted to know what is better performance-wise: to put the conditions in the JOIN? (x JOIN y on x.hi = y.hi AND ....) or maybe to put it under the WHERE, and leave the JOIN only with the crossing condition.

thanks folks

Answer 1

This will depend on the query optimisation engine and how it breaks the query down into its logical query operators.

With MS SQL Server you can view the execution plan but normally there is no difference as the optimiser will see both ways as equivalent.

Answer 2

There is a similar question on StackOverflow here.

It's largely just a matter of which syntax you prefer. My understanding is that the SQL optimizer should be able to evaluate them with little difference in performance.