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
sql left join 多表连接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 后⾯的条件是作为连接之后结果集的筛选 。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。