Multiple Table Select vs. JOIN (performance)

Question

When selecting from multiple tables in MySQL, both of the following queries return the same result set.

Is one of these queries better or more efficient than the other? From my testing on a small dataset (~2k rows in each table) they both return the same result set in around the same execution time.

Query 1:

SELECT     * FROM     products,     product_meta,     sales_rights WHERE      (         products.id = product_meta.product_id         AND products.id = sales_rights.product_id     )     AND (...) LIMIT 0,10; 

Query 2:

SELECT     * FROM     products INNER JOIN product_meta ON products.id = product_meta.product_id JOIN sales_rights ON product_meta.product_id = sales_rights.product_id  WHERE     (...) LIMIT 0,10; 

Answer 1

They are the same, but with a different syntax. So you shouldn't expect any performance difference between the two syntaxes. However the the last syntax(ANS SQL-92 syntax) is the recommended, see these for more details:

• Bad habits to kick : using old-style JOINs.
• SQL JOIN: is there a difference between USING, ON or WHERE?

Answer 2

I think that this thread gives a great explanation.

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 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.