The join version is also certainly less readable for anyone who doesn't normally work with databases and is just dipping into the database handling code for some reason.
I dont usually buy the argument "make this code no longer state the intent that would normally be understood by people who know the programming language, so that it's more readable for people who don't know the programming language"
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.
> 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.
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.
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).
I think it's better at specifying intent, similarly to how you would use "for" and "while" in a programming language even though they are literally the same thing and more often than not they compile to, respectively, identical query plans and identical asm/bytecode.
Also if you work a lot with databases you often need to do outer joins, a full cartesian product is almost never what you want. The join syntax is more practical if you need to change what type of join you are performing, especially in big queries.
I learned that syntax 5 years ago from my boss. I still prefer to use CROSS JOIN just to make it clear to other noobs like me what I'm expecting with the query.
For myself, this is even with a couple of tables valid. I think of the query as "looking up references in a book". Therefore I write global filters in the WHERE and stuff specific to a table in the ON condition.
