INNER JOIN ON vs WHERE clause

Question

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) 

Or else:

SELECT     table1.this, table2.that, table2.somethingelse FROM     table1 INNER JOIN table2     ON table1.foreignkey = table2.primarykey WHERE     (some other conditions) 

Do these two work on the same way in MySQL?

Answer 1

INNER JOIN is ANSI syntax that 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.

The WHERE syntax is more relational model oriented.

A result of two tables JOINed 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 WHERE syntax.

As for your example, in MySQL (and in SQL generally) these two queries are synonyms.

Also, note that MySQL also has a STRAIGHT_JOIN clause.

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 MySQL using WHERE syntax.

Answer 2

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.