db2中的连接查询,内连接、外连接、交叉连接

[size=large]DB2 连接查询的学习:
[size=large][/size]


首先创建三个表 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 没有了。因为没有用外连接,所以是不会有空行的。[/size]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值