这里以左连接 left join 为例，说明 on 后面的条件与 where 后面条件的区别

创建测试表：TAB_1、TAB_2

create table TAB_1 as (select 'c' as A,'d' as B,'3' as C,'4' as D from dualunion allselect 'a' as A,'b' as B,'1' as C,'2' as D from dual);
create table TAB_2 as (select 'c' as A,'d' as B,'2' as C,'4' as D from dualunion allselect 'a' as A,'b' as B,'1' as C,'3' as D from dual);

 1、在 on 里面限制从表

select * from TAB_1 M left join TAB_2 N 
on M.A=N.A and M.B=N.B
and N.C='1';

2、 在where里面限制从表

select * from TAB_1 M left join TAB_2 N 
on M.A=N.A and M.B=N.B
where N.C='1';

3、 在 on 里面限制主表

select * from TAB_1 M left join TAB_2 N 
on M.A=N.A and M.B=N.B
and M.C='1';

4、在 where 里面限制主表

select * from TAB_1 M left join TAB_2 N 
on M.A=N.A and M.B=N.B
where M.C='1';

5、在 on 里面同时限制主表和从表

select * from TAB_1 M left join TAB_2 N 
on M.A=N.A and M.B=N.B
and M.C='1'
and N.C='4';

 

select * from TAB_1 M left join TAB_2 N 
on M.A=N.A and M.B=N.B
and M.C='4'
and N.C='1';

 

6、在 on 里面限制从表，在where里面限制主表

select * from TAB_1 M left join TAB_2 N 
on M.A=N.A and M.B=N.B
and N.C='4'
where M.C='1';

select * from TAB_1 M left join TAB_2 N 
on M.A=N.A and M.B=N.B
and N.C='1'
where M.C='4';

 

7、在 on 里面限制主表，在 where 里面限制从表

select * from TAB_1 M left join TAB_2 N 
on M.A=N.A and M.B=N.B
and M.C='1'
where N.C='4';

select * from TAB_1 M left join TAB_2 N 
on M.A=N.A and M.B=N.B
and M.C='4'
where N.C='1';

总结：

通过以上实例，不难看出 on 后面的条件只会作为连接条件进行限定，并不影响结果集，结果集依然按照 left join 的特性进行输出。where 后面的条件是作为连接之后结果集的筛选 。