For simplicity, assume all relevant fields are NOT NULL.
You can do:
SELECT table1.this, table2.that, table2.somethingelse FROM table1, table2 WHERE table1.foreignkey = table2.primarykey AND (some other conditions)
SELECT table1.this, table2.that, table2.somethingelse FROM table1 INNER JOIN table2 ON table1.foreignkey = table2.primarykey WHERE (some other conditions)
Are those two worked on the same way by MySQL?
INNER JOIN is
ANSI syntax which you should use.
It is generally considered more readable, especially when you join lots of tables.
It can also be easily replaced with an
OUTER JOIN whenever a need arises.
WHERE syntax is more relational model oriented.
A result of two tables
JOIN‘ed is a cartesian product of the tables to which a filter is applied which selects only those rows with joining columns matching.
It’s easier to see this with the
As for your example, in
MySQL (and in
SQL generally) these two queries are synonyms.
Also note that
MySQL also has a
Using this clause, you can control the
JOIN order: which table is scanned in the outer loop and which one is in the inner loop.
You cannot control this in
Others have pointed out that INNER JOIN helps human readability, and that’s a top priority; I agree. Let me try to explain why the join syntax is more readable.
A basic SELECT query is this:
SELECT stuff FROM tables WHERE conditions
The SELECT clause tells us what we’re getting back; the FROM clause tells us where we’re getting it from, and the WHERE clause tells us which ones we’re getting.
JOIN is a statement about the tables, how they are bound together (conceptually, actually, into a single table). Any query elements that control the tables – where we’re getting stuff from – semantically belong to the FROM clause (and of course, that’s where JOIN elements go). Putting joining-elements into the WHERE clause conflates the which and the where-from; that’s why the JOIN syntax is preferred.