Post History

Some condition require you to put conditions in the join clause.

For example, if you're doing a LEFT OUTER JOIN, and you want to match all rows from table A with _only_ the rows from table B with the corresponding id and another condition, then you must put the condition in the join clause.

    SELECT * 
    FROM A
    LEFT OUTER JOIN B on a.fk_b = b.pk
      AND b.pk <10000

Because if you had put that condition in the WHERE clause, then `b.pk < 10000` would naturally exclude all cases where the outer join found no matching row, and it would function as an inner join.

Aside from those cases where the logic demands it, I don't think there is a "best practice." It's up to personal preference. 

As you noted, the MySQL query optimizer should behave the same for an inner join, regardless of whether you put the condition in the join clause or the where clause. 

Not all SQL implementations behave this way, though. There could be some that optimize differently depending on the syntax you use. That could be considered a design flaw, but nevertheless, you should be aware of it and test to make sure the implementation you use behaves the way you expect.

My personal preference given the choice is to put conditions in the JOIN clause only if they pertain to the join itself. If there are other conditions that are simply row restrictions, I put them in the WHERE clause. To me, this is more clear and intention-revealing.

When there is no functional reason to prefer one style over the other, it's best to adopt a style and use it as consistently as you can within a given project. The "worst practice" is to flip-flop arbitrarily between different code styles, because this confuses anyone who needs to read or maintain the code. They won't know why you have two different styles, and whether it's important.