一个数据库的实例

数据库中表的连接方法

分类：数据库 2007-05-09 14:13 857人阅读评论(2) 收藏举报

在很多应用程序的开发中，都离不开数据库，而对数据库的查询操作则是数据库应用中一个非常重要的功能。数据库由许多表，视图，存储过程等等组成。其中表是数据库的基本组成元素，数据库查询其实就是对数据库中的表进行查询操作。

如果是对一张表进行查询，那自然是非常简单，只需要对查询的表指定输出字段和查询条件即可，但是实际应用中往往都是同时对多个表进行查询操作，只取出各个表中所需的一部分字段的信息。那我们怎么把各个表中的信息连接起来呢？这就是本文所要讲述的表的连接方法。

首先，为了方便说明，我们先创建两个表，并输入一些数据：

A表：

idname

01A01

02A02

03A03

B表：

idname

01B01

02B02

04B04

简单地说，两个表的连接方法有以下四种（当然一个表也可以同时和多个表进行表连接，但实质还是两个表之间的连接，只是由多个两个表的连接组成而已）：

1．内部连接（inner join），简称内连

用指定的关键字段将两个表连接起来，只有关键字段同时在两个表中的存在的记录才输出。如果把A表的连接条件看成集合TA，B表的连接条件看成集合TB的话，则A，B内部连接的结果即为TA，TB两集合的交集。

比如将上面的A，B表用id内部连接起来，即A.id = B.id，得到的结果如下：

A.id

https://www.360docs.net/doc/e09257878.html,

0101A01B01

0202A02B02

如上所述，A表的id和B表的id的交集是{01,02}，故得到上面的结果。

得到结果的记录集中，连接关键字段的值一定相等。

2．左部连接（left join），简称左连

用指定的关键字段将两个表连接起来，关键字段存在于第一个表中的记录都输出，如果该关键字段在第二个表中不存在，那么B表中对应的字段的值用空值(NULL)来填充。如果把A表的连接条件看成集合TA，B表的连接条件看成集合TB的话，则A，B左部连接的结果即为TA。比如将上面的A，B表用id左部连接起来，即A.id = B.id，得到的结果如下：

A.id

https://www.360docs.net/doc/e09257878.html,

0101A01B01

0202A02B02

03NULL A03NULL

如上所述，A表的id的集合是{01,02,03}，故得到上面的结果。

得到结果的记录集中，B表的关键字段要么与A表相等,要么为空值。

3．右部连接（right join），简称右连

用指定的关键字段将两个表连接起来，关键字段存在于第二个表中的记录都输出，如果该关键字段在第一个表中不存在，那么第一个表中对应的字段的值用空值(NULL)来填充。如果把A表的连接条件看成集合TA，B表的连接条件看成集合TB的话，则A，B右部连接的结果即为TB。

比如将上面的A，B表用id右部连接起来，即A.id = B.id，得到的结果如下：

A.id

https://www.360docs.net/doc/e09257878.html,

0101A01B01

0202A02B02

NULL04NULL B04

如上所述，B表的id的集合是{01,02,04}，故得到上面的结果。

得到结果的记录集中，A表的关键字段要么与B表相等,要么为空值。

4．全连接（full join），简称全连

用指定的关键字段将两个表连接起来，只要关键字段存在于任何一个表中的记录都输出，如果关键字段在第一个表中存在而在第二个表中不存在，那么第二个表中对应的字段的值用空值(NULL)来填充，如果关键字段在第二个表中存在而在第一个表中不存在，那么第一个表中对应的字段的值用空值(NULL)来填充。如果把A表的连接条件看成集合TA，B表的连接条件看成集合TB的话，则A，B外部连接的结果即为TA，TB两集合的并集。

比如将上面的A，B表用id全连接起来，即A.id = B.id，得到的结果如下：

A.id

https://www.360docs.net/doc/e09257878.html,

0101A01B01

0202A02B02

03NULL A03NULL

NULL04NULL B04

如上所述，A表的id和B表的id的并集是{01,02,03,04}，故得到上面的结果。

得到结果的记录集中，连接关键字段的值要么相等，要么其中一个为空值，另一个不为空值。在上面四种连接方法中，左连和右连是相对的，如果将表的位置换一下，那左连的就应该用右连，右连的就应该用左连了，左连和右连统称为外部连接，简称外连。还有就是连接的关键字段也可以有多个，可以用AND来连接起来。

下面我们以一个简单的学生选课数据库为例，来进行说明。

创建学生表

CREATE TABLE STUDENT(ID CHAR(10) PRIMARY KEY,NAME VARCHAR(20),SEX CHAR(1),BIRTH_DAY DATE)

创建课程表

CREATE TABLE COURSE(ID CHAR(10) PRIMARY KEY,NAME VARCHAR(20))

创建选课表

CREATE TABLE STUDENT_COURSE(STUDENT_ID CHAR(10), COURSE_ID CHAR(10))

其中学生表，课程表为主表，选课表为学生与课程之间的关系表。

首先，如果我们要查询所有选了课的学生的姓名以及该学生所选的课程id的话，就需要对学生表和课程表进行内连。SQL语句如下：

SELECT

A.ID,https://www.360docs.net/doc/e09257878.html, ,

B.COURSE_ID

FROM

STUDENT A INNER JOIN STUDENT_COURSE B ON A.ID = B.STUDENT_ID

在ORACLE中也可以这样写：

SELECT

A.ID,https://www.360docs.net/doc/e09257878.html, ,

B.COURSE_ID

FROM

STUDENT A ,

STUDENT_COURSE B

WHERE

A.ID =

B.STUDENT_ID

在上面的SQL中我们得到的学生只是选了课的学生的选课信息，如果需要输出全部学生的信息，而不管该学生是否选了课的话，就需要使用左连接，当然右连也可以，只是相对而言。那么SQL则应该这样写：

SELECT

A.ID,https://www.360docs.net/doc/e09257878.html, ,

B.COURSE_ID

FROM

STUDENT A LEFT JOIN STUDENT_COURSE B ON A.ID = B.STUDENT_ID

在ORACLE中也可以这样写：

SELECT

A.ID,https://www.360docs.net/doc/e09257878.html, ,

B.COURSE_ID

FROM

STUDENT A ,

STUDENT_COURSE B

WHERE

A.ID =

B.STUDENT_ID(+)

用右连的话，则调换一下两个表的位置：

SELECT

A.ID,https://www.360docs.net/doc/e09257878.html, ,

B.COURSE_ID

FROM

STUDENT_COURSE B RIGHT JOIN STUDENT A ON B.STUDENT_ID = A.ID

在ORACLE中也可以这样写：

SELECT

A.ID,https://www.360docs.net/doc/e09257878.html, ,

B.COURSE_ID

FROM

STUDENT A ,

STUDENT_COURSE B

WHERE

B.STUDENT_ID(+) = A.ID

在学生表和选课表中，为了方便说明，假设并不是所有的所有的学生的信息都被登录到学生表中，也就是说，在选课表里有的学生并不一定在学生表中有。这时要想得到所有学生的选课信息的话，就需要对学生表和选课表的学生信息进行合并。这种情况则需要使用全连。SQL 语句则应该这样写：

SELECT

DECODE(A.ID,NULL,B.STUDENT_ID,A.ID) ID,

https://www.360docs.net/doc/e09257878.html, ,

B.COURSE_ID

FROM

STUDENT A FULL JOIN STUDENT_COURSE B ON A.ID = B.STUDENT_ID

※但是请注意，ORACLE中并没有诸如下面的条件表达式来表示全连接。

A.ID(+) =

B.STUDENT_ID(+)

在取出的学生ID的时候，用了下面的表达式：

DECODE(A.ID,NULL,B.STUDENT_ID,A.ID)

就是说学生表的ID为空值的时候，也就是那个学生在选课表里有，而在学生表里没有的时候，则输出选课表中的学生ID，也就得到了学生表和选课表的学生ID的并集。

实际上，使用表的连接有时候往往会带来很多便利和意想不到的效果。

比如上面的学生表中，如果要找出所有年龄不超过一岁的组合的话，可以将学生表的两个实例进行内连，

SELECT

A.ID AID,

B.ID BID,A.BIRTH_DAY,B.BIRTH_DAY

FROM

STUDENT A INNER JOIN STUDENT B ON A.ID <> B.ID

AND ( (A.BIRTH_DAY <= B.BIRTH_DAY AND ADD_MONTHS(A.BIRTH_DAY,12)>=B.BIRTH_DAY) OR

(B.BIRTH_DAY <= A.BIRTH_DAY AND ADD_MONTHS(B.BIRTH_DAY,12)>=A.BIRTH_DAY) )

当然还可以指定更多的条件，比如性别等等。