数据库的连接(out和inner)操作总结

参考《INFORMIX-SQL关系数据库管理系统技术备查2.10版》P21;P87;P189

外连接:

两个表之间的外连接不对称地处理两个表。一个表为主的(常以"被保存的"提到),而另一个是从属的。如果从属表无满足连接条件的记录,则外连接在映射所要求的字段前将含NULL值的记录连接到主表记录上。为了具体说明它,令a为tab1的字段,b为tab2的字段。此外,还令两个表的值如下所示:

tab1.a tab2.b
2 4
3 2
5 6
 5

RDSQL语法要求外连接中的从属表由FROM子句中的关键字OUTER处理。下面的SELECT语句包含tab1(主表)和tab2(从表)之间的外连接:

select a,b from tab1, out tab2 where a=b;

结果表具有下列两字段:
a b
2 2
3 –
5 5

a的每一个值是列出的,而只有匹配a值的那些b值列出。如果在字段b中无值满足连接条件,则用NULL值(在此以"-"表示)替代。

在外连接的情形下要求WHERE子句,且在其中必须设置两个表之间的条件关系。

问题:

一、你查查程序,里面好像有对同一个表使用outer的情况,看看是为了什么?

大致有两种情况:

1、在as_X31Value.ec中:

    EXEC SQL declare Cur1_hytjj_X31 cursor
        for select a.hybm, a.sdssl,
                   b.zzc, b.zfz,b.lrze,b.ksze,
                   c.zzc, c.zfz
            from p_bxthybm a, outer(s_tjsj1 b), outer(s_tjsj1 c)
            where a.sjlybm[1,1]=’3′
            and a.qssj <= :lmsj_t.lmCssj
            and a.zzsj >= :lmsj_t.lmCssj
            and b.ten_id = :lmsj_t.lmTen_id
            and b.hybm = a.hybm
            and c.ten_id = :lmsj_t.lmJqten_id
            and c.hybm = a.hybm
   and a.hybm <> :gdhybm;

outer连接两个相同的表,目的是:

分别取出本期对应的起始期和终止期(b.zzc, b.zfz);以及基期对应的起始期和终止期(c.zzc, c.zfz)。

2、在X_zhmk1.ec中:

        EXEC SQL select a.hybm,   a.zbfxfz as bqfxfz,
                        a.yqssl,  a.dqxdye,
                        a.sjycbm, b.zbfxfz as jqfxfz
             from r_hyzhpj a, outer(r_hyzhpj b)
             where a.bgq=:lmsj_t.lmBgq
             and   a.mkbm="X00"
             and   a.lsh=:ddjk_t.igPlh
             and   a.yxbz=0
             and   b.bgq=:lmsj_t.lmJqbgq
             and   b.mkbm="X00"
             and   b.yxbz=0
             and   b.hybm=a.hybm
             into temp tmp_hyzh with no log;

outer连接两个相同的表,且条件均相同,目的是:

分别取出本期风险分值(a.zbfxfz as bqfxfz)和基期风险分值(b.zbfxfz as jqfxfz)。

二、使用outer与不使用outer有什么区别?最好是能够总结一下,什么情况下使用什么连接方式?特别是主表、从表出现一对多的情况。

好像在informix中只有两种情况:

1、不含有outer的两个表以上的连接:
分为a、两个表的连接;b、多重连接;c、自连接;

它的结果集是:参与连接的表做笛卡儿乘积,其中符合条件的字段才会被select出来;不符合条件的字段不会被select出来。也就是说,如果没有符合条件的字段,则结果集为空。

2、包含outer的两个表以上的连接:

两个表之间的外连接不对称地处理两个表。一个表为主的(常以"被保存的"提到),而另一个是从属的。如果从属表无满足连接条件的记录,则外连接在映射所要求的字段前将含NULL值的记录连接到主表记录上。也就是说,如果没有符合条件的字段,结果包含主表的所以字段,对应的从表字段置空。

简短地说,从属表比主表的级别低。

另外在使用outer时,主表、从表出现一对多的情况,如下(X_zhmk1.ec):

    EXEC SQL declare Cur_ZhmkZz_X cursor
         for select a.hybm, a.bqfxfz, a.dqxdye, a.yqssl,
                    a.sjycbm, a.jqfxfz, nvl(b.pjqx,0),
                    nvl(c.pjssl,0), nvl(d.pjssl,0),
                    nvl(e.pjssl,0), nvl(f.pjssl,0), nvl(g.pjssl,0),
                    nvl(h.pjssl,0), nvl(i.pjssl,0), nvl(j.pjssl,0)
             from   tmp_hyzh a, outer(hy_dxzb b), outer(hy_yqssl1 c),
                    outer(hy_yqssl2 d), outer(hy_yqssl3 e),
                    outer(hy_yqssl4 f), outer(hy_yqssl5 g),
                    outer(hy_yqssl6 h), outer(hy_yqssl7 i),
                    outer(hy_yqssl8 j)
             where  b.hybm=a.hybm
             and    c.hybm=a.hybm
             and    d.hybm=a.hybm
             and    e.hybm=a.hybm
             and    f.hybm=a.hybm
             and    g.hybm=a.hybm
             and    h.hybm=a.hybm
             and    i.hybm=a.hybm
             and    j.hybm=a.hybm;

此时也是实现了多个表的联合查询,把需要的内容从相关表中取出来。

 

三、为什么要使用outer表?

速度快!

特别是参与连接的表的数目比较多的时候。

普通连接是先将参与连接的各个表进行笛卡儿积,而后从结果表中取出符合where条件的行。
而outer连接是根据主表的行,从从属表中取出符合where条件的对应内容。

四、连接使用的几种情况:

1、普通连接(内部连接):
select a,b from tab1,tab2 where …
其中tab1和tab2直接进行笛卡儿积,而后根据where条件取出需要的行。

2、外连接:
select a,b from tab1, outer tab2 where …
根据tab1的行从tab2中取出符合where条件的字段加入结果集。——布尔关系

3、全部内连接:
select … from x,y,z where …
先进行x和y表的笛卡儿积,并将结果集 与z表再进行一次笛卡儿积; 而后根据where条件取出需要的行。——效率会很慢,为什么不是做完x表和y表的笛卡儿积后就先用where条件取出一个中间结果,而后再与z表笛卡儿积呢?

4、先内后外:
select … from x,y,outer z where …
先进行x和y表的笛卡儿积,再将结果集 与z表进行外连接。

5、两个并列外连接(多个并列外连接)
select … from x,outer y,outer z where …
x和y之间有一种关系,在x和z之间也有一种关系,但是y和z没有关系。(见上面二、2、的例子)
运算流程是:在x和y之间执行外连接,然后在结果表和z之间做外连接。

6、先外后内:
select … from x, outer(y,z) where …
y表和z表的笛卡儿积与x进行外连接。
运算流程可以理解为:根据x表中的行,在表y和表z中找符合where条件的字段,如果y表和z表同时存在符合where条件的字段,则放入结果集中。

7、outer嵌套:
select … from a, outer(y, outer z) where…
x必须与y相关,y必须与z相关
运算流程:选择x中的某条记录,并查看y和z中是否有满足(y, outer z)的一对记录。

五、考虑SQL如何执行一个外部连接时可使用如下步骤:

1、画出与FROM子句相应的图。用符号@替换每个关键字OUTER,并把表名放到这个关键字的下面一级上;
2、确保在WHERE子句中存在一个条件,它将每一级上的某个表通过@与下一级的某个表连接起来;
3、产生在同一级上连接的所以表的笛卡儿乘积,并使用仅用于该级别上的WHERE子句中的那些条件;
4、从第一级开始,每次取结果表的一个记录,然后从下一级的结果表中查找满足WHERE子句的记录。如果不能满足WHERE子句,则用NULL值替代下一级表的字段。

六、关于outer,我测试了一下,可能有这样一种情况
a b
2 2
3 4
4 4
 5

使用
a, outer(b)

结果应该是
2 2
3
4 4
4 4

答:是的。

而且
a b
2 2
3 4
4 4
4 5

使用
a, outer(b)

结果应该是
2 2
3
4 4
4 4
4 4
4 4

欢迎关注我的微信公众号:

 

如无特殊说明,文章均为本站原创,转载请注明出处!

发表评论

邮箱地址不会被公开。 必填项已用*标注