SQL语句理解左右连接和where

这里以左连接 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 dual
	union all
	select '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 dual
	union all
	select '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 后面的条件是作为连接之后结果集的筛选 。