sql - INNER JOIN ON vs WHERE clause

itemprop="text">

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?

class="post-text" itemprop="text">

class="normal">Answer

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.

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.