Oracle关于LEFT JOIN...ON...AND...及LEFT JOIN...ON...WHERE...的区别
关于left join...on...and...以及left join...on...where的区别，网上很多的说法是对于left join...on...and...，and所限定的条件只对关联字段起作用，比如select a.* from tmp_table_a a left join tmp_table_b b on a.col1=b.col1 and b.col2=xx,很多人认为条件b.col2=xx是不起作用的。
对于这种说法，我个人是不认同的，至少来讲，这是一种不负责任的说法。以下是我的一些测试例子，从这里大家就可以理解这两个用法的区别。
select count(*) from tb_bo_valusr_new where month=201010 and brand=3;--141858
select count(*) from tb_bo_valusr_new where month=201010;---2281189
select count(distinct usr_nbr) from tb_bo_valusr_new where brand=3;--152110
select count(distinct usr_nbr) from tb_bo_valusr_new where month=201010;--2281189
select count(*) from tb_bo_valusr_new;--4602747
select count(*) from tmp_msy_bj_001;--986843
select count(*) from tmp_msy_bj_001 where if_wlg='是';--272623
--例1
create table tmp_msy_bj_007
as select
a.usr_nbr,b.cmcc_branch_cd from
tmp_msy_bj_001 a
left join tb_bo_valusr_new b on a.usr_nbr=b.usr_nbr;
select count(*) from tmp_msy_bj_007;--1957872
select count(distinct usr_nbr) from tmp_msy_bj_007;---986843
select count(*) from (select distinct * from tmp_msy_bj_007);--1024792
--例2
create table tmp_msy_bj_008
as select
a.usr_nbr,b.cmcc_branch_cd from
tmp_msy_bj_001 a
left join tb_bo_valusr_new b on a.usr_nbr=b.usr_nbr and b.month=201010;
select count(*) from tmp_msy_bj_008;--986843
select count(distinct usr_nbr) from tmp_msy_bj_008;--986843
select count(*) from (select distinct * from tmp_msy_bj_008);--986843
--例3
create table tmp_msy_bj_006
as select
a.usr_nbr,b.cmcc_branch_cd from
tmp_msy_bj_001 a
left join tb_bo_valusr_new b on a.usr_nbr=b.usr_nbr and b.month=201010
where a.if_wlg='是';
select count(*) from tmp_msy_bj_006;--272623
select count(distinct usr_nbr) from tmp_msy_bj_006;--272623
select count(*) from (select distinct * from tmp_msy_bj_006);--272623
--例4
create table tmp_msy_bj_005
as select
a.usr_nbr,b.cmcc_branch_cd from
tmp_msy_bj_001 a
left join tb_bo_valusr_new b on a.usr_nbr=b.usr_nbr and b.month=201010 and b.brand=3;
select count(*) from tmp_msy_bj_005;--986843
select count(distinct usr_nbr) from tmp_msy_bj_005;--986843
select count(*) from(select distinct * from tmp_msy_bj_005);--986843
--例5
create table tmp_msy_bj_003
as select
a.usr_nbr,b.cmcc_branch_cd,b.brand from
tmp_msy_bj_001 a
left join tb_bo_valusr_new b on a.usr_nbr=b.usr_nbr and b.brand=3;
select count(*) from tmp_msy_bj_003;--1062507
select count(distinct usr_nbr) from tmp_msy_bj_003;--986843
select count(*) from (select distinct * from tmp_msy_bj_003);--991560
select count(*) from tmp_msy_bj_003 where brand=3;--154124
select count(distinct usr_nbr) from tmp_msy_bj_003 where brand=3;--78460
select count(*) from tmp_msy_bj_003 where brand is null;--908383
--例6
create table tmp_msy_bj_002
as select
a.usr_nbr,b.cmcc_branch_cd from
tmp_msy_bj_001 a
left join tb_bo_valusr_new b on a.usr_nbr=b.usr_nbr where b.brand=3;
select count(*) from tmp_msy_bj_002;---154124
select count(distinct usr_nbr) from tmp_msy_bj_002;--78460
select count(*) from(select distinct * from tmp_msy_bj_002);--83177
--例7
create table tmp_msy_bj_011