【SQL】条件语句写在 on 后与写在 where 后的区别

参考原文：Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]

 

下面举一个为总统投票的例子：

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%肯定，读者自行判断）。

 

总结：

1. On： 当 join 在查找匹配的行的时候进行过滤 
   Where：当join 结束之后对结果进行过滤  
   
2. 如果是Left Join 的话，不能随便把 where 的条件移动到 on 后， 但是如果是 Inner Join 的话就很可能可以。