为什么以及何时在 WHERE 子句中带有条件的 LEFT JOIN 不等同于 ON 中的相同 LEFT JOIN?

2021-12-10 00:00:00 sql left-join tsql sql-server

我遇到了一个非常令人困惑的情况，这让我怀疑我对 SQL Server 中的联接的所有理解.

I'm experiencing a very confusing situation that makes me question all my understanding of joins in SQL Server.

SELECT t1.f2 
FROM   t1 
LEFT JOIN t2 
ON t1.f1 = t2.f1 AND cond2 AND t2.f3 > something

不会给出与以下相同的结果:

Does not give the same results as :

SELECT t1.f2 
FROM   t1 
LEFT JOIN t2 
ON t1.f1 = t2.f1 AND cond2 
WHERE  t2.f3 > something

能否请人帮忙告诉这两个查询是否应该等效?

Can please someone help by telling if this two queries are supposed to be equivalent or not?

谢谢

推荐答案

on 子句用于 join 寻找匹配的行.where 子句用于在所有连接完成后过滤行.

The on clause is used when the join is looking for matching rows. The where clause is used to filter rows after all the joining is done.

以迪士尼卡通人物为总统投票的例子:

An example with Disney toons voting for president:

declare @candidates table (name varchar(50));
insert @candidates values 
    ('Obama'), 
    ('Romney');
declare @votes table (voter varchar(50), voted_for varchar(50));
insert @votes values 
    ('Mickey Mouse', 'Romney'),
    ('Donald Duck', 'Obama');

select  *
from    @candidates c
left join    
        @votes v
on      c.name = v.voted_for
        and v.voter = 'Donald Duck'

即使 Donald 没有投票给他，这仍然会返回 Romney.如果将条件从 on 移动到 where 子句:

This still returns Romney even though Donald didn't vote for him. If you move the condition from the on to the where clause:

select  *
from    @candidates c
left join    
        @votes v
on      c.name = v.voted_for
where   v.voter = 'Donald Duck'

Romney 将不再出现在结果集中.

Romney will no longer be in the result set.

相关文章