Q:

Is it better to use where instead of adding conditions into join clause in SQL queries?

Hello :) I've got a question on MySQL queries.
Which one's faster and why?
Is there any difference at all?
select tab1.something, tab2.smthelse
from tab1 inner join tab2 on tab1.pk=tab2.fk 
WHERE tab2.somevalue = 'value'

Or this one:
select tab1.something, tab2.smthelse
from tab1 inner join tab2 on tab1.pk=tab2.fk 
AND tab2.somevalue = 'value'

A:

As Simon noted, the difference in performance should be negligible.  The main concern would be ensuring your query correctly expresses your intent, and (especially) you get the expected results.
Generally, you want to add filters to the JOIN clause only if the filter is a condition of the join.  In most (not all) cases, a filter should be applied to the WHERE clause, as it is a filter of the overall query, not of the join itself.
AFAIK, the only instance where this really affects the outcome of the query is when using an OUTER JOIN.
Consider the following queries:
SELECT *
FROM Customer c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
WHERE o.OrderType = "InternetOrder"

vs.
SELECT *
FROM Customer c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId AND o.OrderType = "InternetOrder"

The first will return one row for each customer order that has an order type of "Internet Order".  In effect, your left join has become an inner join because of the filter that was applied to the whole query (i.e. customers who do not have an "InternetOrder" will not be returned at all).
The second will return at least one row for each customer.  If the customer has no orders of order type "Internet Order", it will return null values for all order table fields.  Otherwise it will return one row for each customer order of type "Internet Order".

