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;

Decent DBMS should have a query optimizer that can digest the input query and figure out the best way to run it. So a query optimizer should handle the queries you mentioned equally. — Muhammad Gelbana, Jul 25 '17 at 09:40
In my setup Oracle SQL Developer 3.1.06, the "select from t1, t2, t3 where" is 0,016s faster than the equivalent one with "select from t1 inner join t2 on...": 0,094s vs 0,109s. Although there is a loss in readability as the answers point out. — EuripidesL, Apr 15 '21 at 07:04

score 54 · Accepted Answer · edited Jun 03 '18 at 17:17

54

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:

edited Jun 03 '18 at 17:17

gopi1410

6,567
9
41
75

answered Nov 20 '12 at 15:06

Mahmoud Gamal

78,257
17
139
164

score 6 · Answer 2 · edited May 23 '17 at 11:54

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.

Multiple Table Select vs. JOIN (performance)

2 Answers2

Linked