为什么以及当WHERE子句中具有条件的LEFT JOIN与ON中的同一个LEFT JOIN不相等时，为什么?

问题描述

我遇到了一个非常混乱的情况，这使我对我对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 

给出的结果与:: p不相同

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?

Thx

推荐答案

当join寻找匹配的行时，将使用on子句. 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'

这仍然返回Romney，即使Donald没有投票给他.如果将条件从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.

这篇关于为什么以及当WHERE子句中具有条件的LEFT JOIN与ON中的同一个LEFT JOIN不相等时，为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！