参见英文答案 >
SQL join: where clause vs. on clause13个
> Difference between “on .. and” and “on .. where” in SQL Left Join?5个
我遇到一个非常混乱的情况,让我质疑我对sql Server中加入的所有理解.
> Difference between “on .. and” and “on .. where” in SQL Left Join?5个
我遇到一个非常混乱的情况,让我质疑我对sql Server中加入的所有理解.
SELECT t1.f2 FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f1 AND cond2 AND t2.f3 > something
没有得到相同的结果：
SELECT t1.f2 FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f1 AND cond2 WHERE t2.f3 > something
告诉某人是否应该等同于这两个查询？
谢谢
解决方法
当连接正在寻找匹配的行时,使用on子句. where子句用于在所有加入完成后过滤行.
迪斯尼投票总统的例子：
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'
即使唐纳德也没有投票给他,这仍然是罗姆尼.如果将条件从on移到where子句：
select *
from @candidates c
left join
@votes v
on c.name = v.voted_for
where v.voter = 'Donald Duck'
罗姆尼将不再在结果集中.
