下面举一个为总统投票的例子:
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');
-- 查询 1
select *
from @candidates c
left join
@votes v
on c.name = v.voted_for
and v.voter = 'Donald Duck'
-- 查询 2
select *
from @candidates c
left join
@votes v
on c.name = v.voted_for
where v.voter = 'Donald Duck'
-- 查询 3
select *
from @candidates c
left join
@votes v
on c.name = v.voted_for and c.name = 'Romney'
结果如下:
查询1 依然会返回 Romney尽管 Donald 没有给他投票。
查询2 就不会有 Romney了。
查询3 因为我指明候选者是Romney了,所以Obama 后面的列全部为空
on 影响的是笛卡儿积, where影响的是最终的结果集。SQL Server 在执行join 的时候会检查on后的条件, 满足的话才会参与笛卡儿积的生成,对于inner join 如果不满足,连左表的数据也不参与,对于 left join 左表数据会直接搬来,右表字段全部为null。
所以对于 left join 不管你on后面的条件是怎样的,左表数据都会出来,不同的只是右表的数据(为空或者满足on后条件的值)。这种情况尤其是你在只select 左表的数据的时候最明显,你会发现返回的数据总是一样的。
对于inner join,不管你是on后面的条件还是where 后面的条件,只有满足条件的时候才会出来,所以 where子句后的筛选条件基本和on后面的一致 (这一点本人不是100%肯定,读者自行判断)。
总结:
- On: 当 join 在查找匹配的行的时候进行过滤
Where:当join 结束之后对结果进行过滤
- 如果是Left Join 的话,不能随便把 where 的条件移动到 on 后, 但是如果是 Inner Join 的话就很可能可以。