WHERE子句或ON子句中的INNER JOIN条件? [英] INNER JOIN condition in WHERE clause or ON clause?

问题描述

我今天输错了一个查询，但是它仍然有效，并给出了预期的结果.我打算运行此查询:

I mistyped a query today, but it still worked and gave the intended result. I meant to run this query:

SELECT e.id FROM employees e JOIN users u ON u.email=e.email WHERE u.id='139840'

但是我不小心跑了这个查询

but I accidentally ran this query

SELECT e.id FROM employees e JOIN users u ON u.email=e.email AND u.id='139840'

(请注意最后一个子句中的AND而不是WHERE)

(note the AND instead of WHERE in the last clause)

都从用户ID中返回了正确的员工ID.

and both returned the correct employee id from the user id.

这两个查询有什么区别?第二种形式是否仅联接满足条件的2个表的成员，而第一种形式将联接整个表，然后运行查询?一个效率比另一个效率高吗?还有其他我想念的东西吗?

What is the difference between these 2 queries? Does the second form only join members of the 2 tables meeting the criteria, whereas the first one would join the entire table, and then run the query? Is one more or less efficient than the other? Is it something else I am missing?

谢谢！

推荐答案

对于这种内部联接，它们在逻辑上是等效的.但是，您可能会遇到join子句中的条件与where子句中的条件有所不同的情况.

For inner joins like this they are logically equivalent. However, you can run in to situations where a condition in the join clause means something different than a condition in the where clause.

作为一个简单的例子，想象一下您像这样进行左连接；

As a simple illustration, imagine you do a left join like so;

select x.id
from x
       left join y
         on x.id = y.id
;

在这里，我们要从x中获取所有行，而不管y中是否有匹配的id.现在说我们的加入条件越来越大-我们不仅在基于id的y中寻找匹配项，而且还在基于id_type的情况中寻找匹配项.

Here we're taking all the rows from x, regardless of whether there is a matching id in y. Now let's say our join condition grows - we're not just looking for matches in y based on the id but also on id_type.

select x.id
from x
       left join y
         on x.id = y.id
         and y.id_type = 'some type'
;

同样，这将给出x中的所有行，而不管y中是否存在匹配的(id，id_type).

Again this gives all the rows in x regardless of whether there is a matching (id, id_type) in y.

这是非常不同的:

select x.id
from x
       left join y
         on x.id = y.id
where y.id_type = 'some type'
;

在这种情况下，我们选择x的所有行，并尝试与y的行匹配.现在，对于y中不匹配的行，y.id_type将为null.因此，y.id_type ='some type'不被满足，因此那些没有匹配项的行将被丢弃，从而有效地将其转换为内部联接.

In this situation, we're picking all the rows of x and trying to match to rows from y. Now for rows for which there is no match in y, y.id_type will be null. Because of that, y.id_type = 'some type' isn't satisfied, so those rows where there is no match are discarded, which effectively turned this in to an inner join.

长话短说:对于内部联接而言，条件在哪里都无所谓，对于外部联接而言，则可以.

Long story short: for inner joins it doesn't matter where the conditions go but for outer joins it can.

这篇关于WHERE子句或ON子句中的INNER JOIN条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！