多表选择与JOIN（性能）

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

从MySQL中的多个表中进行选择时，以下两个查询都返回相同的结果集。

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.

其中一个查询比另一个查询更好还是更有效？从我对小数据集的测试（每个表中约2k行），它们都在相同的执行时间内返回相同的结果集。

Query 1:

查询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:

查询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;

2 个解决方案

#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:

它们是相同的，但语法不同。所以你不应该期望两种语法之间有任何性能差异。但是建议使用最后一种语法（ANS SQL-92语法），有关详细信息，请参阅以下内容：

• Bad habits to kick : using old-style JOINs.
• 糟糕的习惯：使用旧式JOIN。
• SQL JOIN: is there a difference between USING, ON or WHERE?
• SQL JOIN：USING，ON或WHERE有区别吗？

#2

I think that this thread gives a great explanation.

我认为这个帖子给出了很好的解释。

INNER JOIN is ANSI syntax which you should use.

INNER JOIN是您应该使用的ANSI语法。

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.

只要有需要，它也可以很容易地用OUTER JOIN替换。

The WHERE syntax is more relational model oriented.

WHERE语法更加面向关系模型。

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.

两个表JOIN'ed的结果是应用过滤器的表的笛卡尔积，其仅选择具有连接列匹配的那些行。

It's easier to see this with the WHERE syntax.

使用WHERE语法更容易看到这一点。

As for your example, in MySQL (and in SQL generally) these two queries are synonyms.

至于你的例子，在MySQL（以及一般的SQL）中，这两个查询是同义词。

Also note that MySQL also has a STRAIGHT_JOIN clause.

另请注意，MySQL还有一个STRAIGHT_JOIN子句。

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.

使用此子句，您可以控制JOIN顺序：在外循环中扫描哪个表，在内循环中扫描哪个表。

You cannot control this in MySQL using WHERE syntax.

您无法使用WHERE语法在MySQL中控制此操作。