db2中的连接查询，内连接、外连接、交叉连接示例

不信网恋

DB2 连接查询的学习：
 
首先创建三个表 student(id integer not null,name varchar(32),sex integer) ;
                            score(name varchar(32),score decimal);
                            grade(score double,grade varchar(2));
       学生表，分数表，等级表。
 

 db2 => select * from student
ID          NAME                             SEX
----------- -------------------------------- -----------
          1 xiaoming                                   1
          2 vicky                                      1
          3 xiaohong                                   0
          4 xiaogang                                   1
          5 linlin                                     0
          6 xiaoyue                                    0
          7 manman                                     0
          8 penpen                                     1
          9 hanghang                                   1
         10 bingbing                                   0
  10 条记录已选择。

 
 
 db2 => select * from score
NAME                             SCORE
-------------------------------- -------
xiaoming                           81.00
vicky                             100.00
xiaohong                           90.00
xiaoli                             90.00
  4 条记录已选择。
 
db2 => select * from grade
SCORE   GRADE
-------    -----
 100.00  A
  90.00  B
  80.00  C
  60.00  D
   0.00   X
  5 条记录已选择。
 
 
 

　　连接查询开始
1.       查询出student 和 score 相同姓名的所有列。要求只列出完全相同的列。
内连接：
select s.id,s.name,s.sex,c.score from student s inner join score c on s.name = c.name
 此时外表是基表 即 from后的student表是基表。
 db2 => select s.id,s.name,s.sex,c.score from student s inner join score c on s.name = c.name
ID          NAME                             SEX         SCORE
----------- -------------------------------- ----------- -------
          1 xiaoming                                 1     81.00
          2 vicky                                      1     100.00
          3 xiaohong                                 0     90.00
  3 条记录已选择。
 
 
或者使用where 连接：
select s.id,s.name,s.sex,c.score from student s,score c where s.name = c.name
 
db2 => select s.id,s.name,s.sex,c.score from student s,score c where s.name = c.name
ID          NAME                             SEX         SCORE
----------- -------------------------------- ----------- -------
          1 xiaoming                                   1   81.00
          2 vicky                                      1  100.00
          3 xiaohong                                   0   90.00
  3 条记录已选择。
 
 
2.       外连接查询：
左连接：
select s.id,s.name,c.name,c.score from student s left join score c on s.name = c.name
首先看一下上面的sql。
 
 db2 => select s.id,s.name,c.name,c.score from student s left join score c on s.name = c.name
ID          NAME                             NAME                             SCORE
----------- -------------------------------- -------------------------------- -------
          1 xiaoming                         xiaoming                           81.00
          2 vicky                            vicky                             100.00
          3 xiaohong                         xiaohong                           90.00
          4 xiaogang                         -                                      -
          5 linlin                           -                                      -
          6 xiaoyue                          -                                      -
          7 manman                           -                                      -
          8 penpen                           -                                      -
          9 hanghang                         -                                      -
         10 bingbing                         -                                      -
  10 条记录已选择。
 
            
        左连接查询本质是以左边的表，即from后面的表为主要基表查询的。从上面的结果可以看到，左表中的数据全部都查了出来，右边的表中只有有符合 on 后条件的有对应的都列了出来，右表中没有对应左表的数据就补空。即null 。
        再看这一句
        select * from student s left join score c on s.name = c.name where s.id<5
 
db2 => select * from student s left join score c on s.name = c.name where s.id<5
ID          NAME                             SEX         NAME                             SCORE
----------- -------------------------------- ----------- -------------------------------- -------
          1 xiaoming                                   1 xiaoming                           81.00
          2 vicky                                      1 vicky                             100.00
          3 xiaohong                                   0 xiaohong                           90.00
          4 xiaogang                                   1 -                                      -
  4 条记录已选择。
 
 
 
和上面类似。Where条件句中规定了主表的条件。
 
还有，where条件句子也可以规定右表的条件。
如：select * from student s left join score c on s.name = c.name where c.score>80
 
db2 => select * from student s left join score c on s.name = c.name where c.score>80
ID          NAME                             SEX         NAME                             SCORE
----------- -------------------------------- ----------- -------------------------------- -------
          1 xiaoming                                1    xiaoming                           81.00
          2 vicky                                      1    vicky                             100.00
          3 xiaohong                               0    xiaohong                           90.00
  3 条记录已选择。
 
 
同样也会有3条符合的数据列出。但是遍历查询 score 表会发现，
db2 => select * from score
NAME                             SCORE
-------------------------------- -------
xiaoming                           81.00
vicky                             100.00
xiaohong                           90.00
xiaoli                             90.00
  4 条记录已选择。
 
 
name = xiaoli的条目没有列出。这就是因为基表 左表 中没有这一条主记录。
 
同样的更深刻一点，像这个sql语句：
 
select s.id,s.name,c.name,c.score from student s left join score c on s.name = c.name where s.id > 3
 
db2 => select s.id,s.name,c.name,c.score from student s left join score c on s.name = c.name where s.id > 3
ID          NAME                             NAME                             SCORE
----------- -------------------------------- -------------------------------- -------
          4 xiaogang                         -                                      -
          5 linlin                           -                                      -
          6 xiaoyue                          -                                      -
          7 manman                           -                                      -
          8 penpen                           -                                      -
          9 hanghang                         -                                      -
         10 bingbing                         -                                      -
  7 条记录已选择。
 
 
更加证明只要基表 左表 中有符合where 条件的条目就会列出，右表中是否有对应的记录是无关紧要的。这在很多特殊应用的时候都是很有用的哦。
还有这个看看：
select * from student s left join score c on s.name = c.name where c.name = 'xiaoli'
 
db2 => select s.id,s.name,c.name,c.score from student s left join score c on s.name = c.name where c.name = 'xiaoli'
ID          NAME                             NAME                             SCORE
----------- -------------------------------- -------------------------------- -------
  0 条记录已选择。
 
 
select * from score where name = 'xiaoli'
 
db2 => select * from score where name = 'xiaoli'
NAME                             SCORE
-------------------------------- -------
xiaoli                             90.00
  1 条记录已选择。
 
 
 
为什么第一句没有查询出记录？还是那句话，左连接是以左表为基表的。基表中没有的记录，是根本查不到的。
那换成右连接就有了吗？我们来试试。
 
select * from student s right join score c on s.name = c.name where c.name = 'xiaoli'
 
 db2 => select s.id,s.name,c.name,c.score from student s right join score c on s.name = c.name where c.name = 'xiaoli'
ID          NAME                             NAME                             SCORE
----------- -------------------------------- -------------------------------- -------
          - -                                xiaoli                             90.00
  1 条记录已选择。
 
 
 
效果是立竿见影的。呵呵。融会贯通一下吧，右跟左是一样的，自己多试试就能掌握他们了。
 
还有一种叫全外连接，也叫交叉连接。
select s.id,s.name,c.name,c.score from student s full outer join score c on s.name = c.name
 
 
db2 => select s.id,s.name,c.name,c.score from student s full outer join score c on s.name = c.name
ID          NAME                             NAME                             SCORE
----------- -------------------------------- -------------------------------- -------
          1 xiaoming                         xiaoming                           81.00
          2 vicky                            vicky                             100.00
          3 xiaohong                         xiaohong                           90.00
          - -                                xiaoli                             90.00
          4 xiaogang                         -                                      -
          5 linlin                           -                                      -
          6 xiaoyue                          -                                      -
          7 manman                           -                                      -
          8 penpen                           -                                      -
          9 hanghang                         -                                      -
         10 bingbing                         -                                      -
  11 条记录已选择。 
 
效果就等于是把左连接和右连接合到一块。不加where的时候，就是把两个表的条目都列出来，没有对应的条目，左右都可以 空 出来为null。
加个where试试：
本人的理解，
用左连接和右连接以及交叉连接的时候，你先不要带 where 条件句。试一下，查询出来的东西，就把他当作是一个视图。然后你再加where 条件的时候，其实就跟从这张视图中 查询数据是一样的。
就像这两句一样：
select s.id,s.name,c.name,c.score  from student s full outer join score c on s.name = c.name where
 s.id is null
 
db2 => select s.id,s.name,c.name,c.score  from student s full outer join score c on s.name = c.name where s.id is null
ID          NAME                             NAME                             SCORE
----------- -------------------------------- -------------------------------- -------
          - -                                xiaoli                             90.00
  1 条记录已选择。
 
 
 
select s.id,s.name,c.name,c.score  from student s full outer join score c on s.name = c.name where c.name is null
 
db2 => select s.id,s.name,c.name,c.score  from student s full outer join score c on s.name = c.name where c.name is null
ID          NAME                             NAME                             SCORE
----------- -------------------------------- -------------------------------- -------
          4 xiaogang                         -                                      -
          5 linlin                           -                                      -
          6 xiaoyue                          -                                      -
          7 manman                           -                                      -
          8 penpen                           -                                      -
          9 hanghang                         -                                      -
         10 bingbing                         -                                      -
  7 条记录已选择。
 
看看结果就明白了。
 
下面看看这个sql:
 
 
select s1.id,s1.name,s2.id,s2.name,s3.id,s3.name from student s1 left join student s2 on s1.id = (s2.id-1) left join student s3 on s2.id = (s3.id-1)
执行一下上面的sql：
 
 
 
 db2 => select s1.id,s1.name,s2.id,s2.name,s3.id,s3.name from student s1 left join student s2 on s1.id = (s2.id-1) left join student s3 on s2.id = (s3.id-1)
ID          NAME                             ID          NAME                             ID          NAME
----------- -------------------------------- ----------- -------------------------------- ----------- --------------------------------
          1 xiaoming                                   2 vicky                                      3 xiaohong
          2 vicky                                      3 xiaohong                                   4 xiaogang
          3 xiaohong                                   4 xiaogang                                   5 linlin
          4 xiaogang                                   5 linlin                                     6 xiaoyue
          5 linlin                                     6 xiaoyue                                    7 manman
          6 xiaoyue                                    7 manman                                     8 penpen
          7 manman                                     8 penpen                                     9 hanghang
          8 penpen                                     9 hanghang                                  10 bingbing
          9 hanghang                                  10 bingbing                                   - -
         10 bingbing                                   - -                                          - -
  10 条记录已选择。
 
 
这有什么用呢？
看看加一个where后的效果。
select s1.id,s1.name,s2.id,s2.name,s3.id,s3.name from student s1 left join student s2 on s1.id = (s2.id-1) left join student s3 on s2.id = (s3.id-1) where mod(s1.id,3) = 1;
效果如下：
 
db2 => select s1.id,s1.name,s2.id,s2.name,s3.id,s3.name from student s1 left join student s2 on s1.id = (s2.id-1) left join student s3 on s2.id = (s3
id-1) where mod(s1.id,3) = 1
ID          NAME                             ID          NAME                             ID          NAME
----------- -------------------------------- ----------- -------------------------------- ----------- --------------------------------
          1 xiaoming                                   2 vicky                                      3 xiaohong
          4 xiaogang                                   5 linlin                                     6 xiaoyue
          7 manman                                     8 penpen                                     9 hanghang
         10 bingbing                                   - -                                          - -
  4 条记录已选择。
 
 
这样就实现了把一张表的数据 以 横排三列的形式展现了出来。
这种形式，在很多时候还是颇为有用的。比如曾经遇到过用ireport做jasper的时候，就需要这么来展现。因为ireport的detail 区是只支持向下遍历的，用户如果需要这样的三列相连的话，用这个sql就非常有用了。
当然你也可以用这个，
select s1.id,s1.name,s2.id,s2.name,s3.id,s3.name from student s1,student s2,student s3 where mod(s1.id,3)=1 and mod(s2.id,3)=2 and mod(s3.id,3)=0 and s1.id = s2.id-1 and s2.id = s3.id-1

db2 => select s1.id,s1.name,s2.id,s2.name,s3.id,s3.name from student s1,student s2,student s3 where mod(s1.id,3)=1 and mod(s2.id,3)=2 and mod(s3.id,3)
=0 and s1.id = s2.id-1 and s2.id = s3.id-1
ID          NAME                             ID          NAME                             ID          NAME
----------- -------------------------------- ----------- -------------------------------- ----------- --------------------------------
          1 xiaoming                                   2 vicky                                      3 xiaohong
          4 xiaogang                                   5 linlin                                     6 xiaoyue
          7 manman                                     8 penpen                                     9 hanghang
  3 条记录已选择。

 
但是发现没有？最后的id=10 没有了。因为没有用外连接，所以是不会有空行的。