MySQL Join子句与WHERE子句 [英] MySQL Join clause vs WHERE clause

问题描述

通过以下两种方式完成的子句有什么区别?

SELECT * FROM table1 INNER JOIN table2 ON (
    table2.col1 = table1.col2 AND
    table2.member_id = 4
)

我已经将它们与基本查询和EXPLAIN EXTENDED进行了比较，没有发现任何区别.我想知道这里是否有人发现了在更复杂/处理强度更高的环境中的区别.

SELECT * FROM table1 INNER JOIN table2 ON (
    table2.col1 = table1.col2
)
WHERE table2.member_id = 4

解决方案

使用INNER联接，这两种方法将得出相同的结果，并应产生相同的查询计划.

但是，JOIN(描述两个表之间的关系)和WHERE子句(从结果集中删除行)之间在语义上有所不同.这种语义上的差异应告诉您使用哪个.虽然对结果或性能没有影响，但选择正确的语法将帮助您的代码的其他读者更快地理解它.

请注意，如果您使用外部联接而不是内部联接，可能会有所不同.例如，如果将INNER更改为LEFT，并且连接条件失败，则使用第一种方法仍将获得一行，但是如果使用第二种方法，则该行将被滤除(因为NULL不等于4).

What's the difference in a clause done the two following ways?

SELECT * FROM table1 INNER JOIN table2 ON (
    table2.col1 = table1.col2 AND
    table2.member_id = 4
)

I've compared them both with basic queries and EXPLAIN EXTENDED and don't see a difference. I'm wondering if someone here has discovered a difference in a more complex/processing intensive envornment.

SELECT * FROM table1 INNER JOIN table2 ON (
    table2.col1 = table1.col2
)
WHERE table2.member_id = 4

解决方案

With an INNER join the two approaches give identical results and should produce the same query plan.

However there is a semantic difference between a JOIN (which describes a relationship between two tables) and a WHERE clause (which removes rows from the result set). This semantic difference should tell you which one to use. While it makes no difference to the result or to the performance, choosing the right syntax will help other readers of your code understand it more quickly.

Note that there can be a difference if you use an outer join instead of an inner join. For example, if you change INNER to LEFT and the join condition fails you would still get a row if you used the first method but it would be filtered away if you used the second method (because NULL is not equal to 4).

这篇关于MySQL Join子句与WHERE子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！