CODEWITHSUNDEEP
mysql
Christian Owens
Christian Owens

Reputation: 1116

Multiple Table Select vs. JOIN (performance)

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;

Upvotes: 71

Views: 66801

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

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:

Upvotes: 55

Related Questions