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. You can use this in the following manner. It’s more preferable when you are joining two or more tables.

Are you interested in learning SQL from scratch! Have a look at this interesting video on SQL provided by Intellipaat:

•  whenever a need arises, it can also be easily replaced with an OUTER JOIN.

• ‘WHERE’ clause is relational model oriented.

• WHERE syntax makes easier to work. (You can refer to this video to understand ‘WHERE’ syntax concept).

• When you apply a filter on the tables to select only those rows which have joining columns matching then the cartesian product of the two tables will result in tables JOINED.

• For example, in MySQL (and in SQL generally) these two queries are synonyms.

• MySQL also has a STRAIGHT_JOIN clause. By using STRAIGHT_JOIN clause, you can control the JOIN order in this manner: which table is in the inner loop and which one is scanned in the outer loop. But you cannot control this in MySQL when you are using WHERE syntax.