MySQL 多表选择与 JOIN（性能）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13476029/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple Table Select vs. JOIN (performance)
提问by Christian Owens
When selecting from multiple tables in MySQL, both of the following queries return the same result set.
在 MySQL 中从多个表中选择时，以下两个查询返回相同的结果集。
Is one of these queries betteror 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;
采纳答案by Mahmoud Gamal
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 语法），有关更多详细信息，请参阅以下内容：
回答by Joe Meyer
I think that this threadgives 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.
INNER JOIN 是您应该使用的 ANSI 语法。
它通常被认为更具可读性，尤其是当您加入大量表时。
每当需要时，它也可以轻松地替换为 OUTER JOIN。
WHERE 语法更面向关系模型。
两个表 JOIN'ed 的结果是应用过滤器的表的笛卡尔乘积，该过滤器仅选择连接列匹配的那些行。
使用 WHERE 语法更容易看到这一点。
至于您的示例，在 MySQL 中（以及通常在 SQL 中），这两个查询是同义词。
另请注意，MySQL 也有一个 STRAIGHT_JOIN 子句。
使用这个子句，你可以控制JOIN顺序：外循环扫描哪个表，内循环扫描哪个表。
您无法使用 WHERE 语法在 MySQL 中控制它。
