SQL 查询性能 INNER JOIN ON AND 比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15655076/
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
Query Performance INNER JOIN ON AND comparison
提问by Rashidul Islam
I would like to know which one is best regarding performance between the 2 queries stated below or do they perform identically?
我想知道以下 2 个查询之间哪一个是最好的，或者它们的性能是否相同？
First one: [without WHERE clause, just AND with ON]
第一个：[没有 WHERE 子句，只有 AND 和 ON]
SELECT related_tabid AS tabid, label, t.name
FROM relatedlists r
INNER JOIN tab t
ON t.tabid = r.tabid
AND t.name = 'Leads'
AND r.is_active=1 and r.related_tabid <> 0
AND t.is_active=1
ORDER BY label
Second one: [using WHERE clause, AND associated with where instead of ON ]
第二个：[使用 WHERE 子句，AND 与 where 而不是 ON 相关联]
SELECT related_tabid AS tabid, label, t.name
FROM relatedlists r
INNER JOIN tab t
ON t.tabid = r.tabid
WHERE t.name = 'Leads'
AND r.is_active=1 and r.related_tabid <> 0
AND t.is_active=1
ORDER BY label
采纳答案by John Woo
Both queries are the same because the join used is INNER JOIN. INNER JOINbasically it filters only rows that has at least a match on the other table. Even the two tables are interchange, the result is still the same.
两个查询是相同的，因为使用的连接是INNER JOIN。INNER JOIN基本上它只过滤至少与另一个表匹配的行。即使两个表互换，结果还是一样。
But if you are joining them via LEFT JOIN, the two queries are different from each other and will yield different result.
但是，如果您通过 加入它们LEFT JOIN，则这两个查询彼此不同，并且会产生不同的结果。
回答by Andy Lester
It looks to me that the only difference between the two queries is that one has the t.name = 'Leads'in the WHERE clause and one has it in the JOIN clause. Correct?
在我看来，这两个查询之间的唯一区别是，一个t.name = 'Leads'在 WHERE 子句中有 ，另一个在 JOIN 子句中有它。正确的？
There is no difference between the two. The SQL optimizer will handle both of them identically. Do an EXPLAIN on each of them to verify.
两者没有区别。SQL 优化器将以相同的方式处理它们。对他们每个人做一个解释来验证。
回答by 0rochimaru
In theory, the first query is well just be wanting to put text comparison in the 'where', then joins in the definition of the 'logic' of union of tables and the 'where' is only used to specify values that vary when comparing Where t.name = 'Leads'; regards
从理论上讲，第一个查询只是想将文本比较放在“where”中，然后加入表联合的“逻辑”的定义中，而“where”仅用于指定比较时变化的值其中 t.name = 'Leads'; 问候
