[ndepoel]

It's all about clearly stating your intent. With INNER JOIN you're literally saying "I want to join these two tables together on this particular relation and work on the result", while with the more basic WHERE form you're saying "just lump these two tables together and then we'll filter out the rows that we actually want to see". The join becomes more of a happy side-effect with that, rather than the thing you clearly want to do.

Not only does writing your code in such a way that it states your intent make it easier to read for other humans, it also makes it easier for compilers/query planners to understand what you're trying to do and turn it into a more efficient process at run-time. Now query planners are usually pretty good at distilling joins from WHERE clauses, but that form does also make it easier for mistakes to creep in that can murder your query performance in subtle and hard-to-debug ways.

[jmull]

> it also makes it easier for compilers/query planners to understand what you're trying to do

Hopefully that's not true. SQL's a declarative language, where you describe what you want and the system figures out how to do it. If you describe exactly the same thing using two different syntaxes, the system shouldn't really do anything different. That just makes the programmer's job harder.

[sgarland]

Ideally, but not always true. For various versions of MySQL and Postgres, the planner will infer when a semi/anti-join can replace a WHERE [NOT] IN clause, but not always. IIRC there are still edge cases where neither one picks up on it, and so it’s always safer (and much more clear to the reader) to explicitly use WHERE [NOT] EXISTS if that’s what you want.

[renhanxue]

Also, using the ON clause is consistent with the syntax for outer joins, where you have to use it (because there's a very important logical difference between putting the condition in the ON clause vs in the WHERE clause).