Oracle关于LEFT JOIN...ON...AND...及LEFT JOIN...ON...WHERE...的区别(二)

as select

a.usr_nbr,b.cmcc_branch_cd from

tmp_msy_bj_001 a,tb_bo_valusr_new b where a.usr_nbr=b.usr_nbr and b.brand=3;

select count(*) from tmp_msy_bj_011;---154124

select count(distinct usr_nbr) from tmp_msy_bj_011;--78460

select count(*) from(select distinct * from tmp_msy_bj_011);--83177

--例8

create table tmp_msy_bj_009

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 and b.month=201010;

select count(*) from tmp_msy_bj_009;---78421

select count(distinct usr_nbr) from tmp_msy_bj_009;--78421

select count(*) from(select distinct * from tmp_msy_bj_009);--78421

--以上各个表的创建时间都在20秒内完成

--例9

create table tmp_msy_bj_004

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 a.if_wlg='是';

--运行25分钟都还没有结果

在以上本例子中，b表tb_bo_valusr_new表中每个月都包含当月全量号码,且单月号码是没重复的，而a 表tmp_msy_bj_001没有重复记录。在这样的例子中，如果在left join 之后用 where 指定 b的非连接字段的限制条件，已相当于将A与B等值连接了，很明显，这时的left join已失去意义，left join的本义是保留前面表的所有记录，因此说将后一表中非连接字段的限制条件放在where之后是一种愚蠢的做法。而从上面的例5还可以看出,在left join 后指定and b.column=....起的效果是：将a中符合b条件的记录连接，并保留a中其它不符合的记录一次。因此可以这么理解left join ...on...and...：可以保证保留前面表的全部记录，并可取到后面表中符合指定条件的记录。而出现后面表的and...条件不起作用的情况是：后一表即b表的记录集是a表的一个子集。

再从例9中看出，绝对不要把a的限制条件放在left join on...and...指定，那是找死。