主页 > 知识库 > 数据库 > Oracle >

Oracle关于LEFT JOIN...ON...AND...及LEFT JOIN...ON...WHERE...

来源:中国IT实验室 作者:佚名 发表于:2013-06-07 13:18  点击:
关于left joinonand以及left joinonwhere的区别,网上很多的说法是对于left joinonand,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是不起作用
关于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

有帮助
(0)
0%
没帮助
(0)
0%