INNER JOIN ON vs WHERE clause
inner-join, join, mysql, sql
Solution
`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 `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 `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.
Problem
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`?