MySQL 将来自两个表的记录与连接和不连接结合起来

Question

这两个查询有什么区别:

SELECT `threads`.`id` AS `threads.id` , `posts`.`id` , `posts`.`content`
FROM `threads`
JOIN `posts` ON `threads`.`id` = `posts`.`thread_id`

和

SELECT `threads`.`id` AS `threads.id` , `posts`.`id` , `posts`.`content`
FROM `threads` , `posts`
WHERE `threads`.`id` = `posts`.`thread_id`

它们都返回相同的数据。

Answer 1

WHERE JOIN返回的子句过滤结果集，所以这是一个区别。

只要你使用INNER JOIN在任何OUTER JOIN的情况下，性能和执行计划都没有差异查询会产生不同的执行计划。

另请注意 MySql online doc 中所说的内容:

Generally, you should use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set.