oracle外连接中左.右.全连接的区别

本文目录

1.定义

Oracle语句:

from join_table join_type join_table
[ON (join_condition)]

oin_table:参与连接的表名
ioin_tyoe:连接方式

包括 3 种:

内连接:  
(1)等值连接:
(2)自然连接:等值连接去掉重复的行即为自然连接
(3)不等连接:用不等号查询,包括>,>=等

外连接:
(1)左外连接(left outer join或left join);返回结果中除了满足连接条件,还包括join左侧表的所有行;
(2)右外连接(right outer join或right join);
(3)全外连接(full outer join或full join);

外连接会列出 左表(左外连接),右表(右外连接),或者两个表(全外连接) 中所有符合搜索条件的数据行.

交叉连接: 
(cross join)没有where子句,返回表中所有数据行的笛卡尔积.

2.为什么要用外连接

　内连接时,返回的查询结果集中仅包括符合查询条件(where 搜索条件或 having条件)和连接条件的行.

内连接消除与另一个表中的任何行不匹配的行,而外连接扩展了内连接的结果,除返回所有匹配的行外,还会返回一部分或全部不匹配的行,这主要取决于外连接的种类.

3.什么时候会用到外连接

例如:

信息分类存储，例如员工信息表，其对应的每个月的工资领取情况在另一张表中，,这个时候需要外连接进行表关联.

如果要用内连接查询,那么可能一部分没有领取工资的员工相关信息就会被剔除,但是我们想要得到员工的工资领取情况,意思就是可以从查询结果中看到哪些没有领取,哪些员工已经结算.这样,就必须用外连接了.

4.外连接两边都能用么

5.例子:左右连接的查询结果对比

使用工具:SQL Navigator 3+Oracle 8.0

创建语文成绩表:

create table testyw--语文成绩表
(
    id number,
    name varchar(40),
    scoreyw number
)

创建数学成绩表:

create table testsx--数学成绩表
(
    id number,
    name varchar2(40),
    scoresx number
)

逐行插入语句

insert into testyw values(201,'小麦',87)
insert into testyw values(202,'小白',98)
insert into testyw values(203,'小黑',82)
insert into testsx values(202,'小白',81)
insert into testsx values(203,'小黑',67)

插入结束之后查询两表信息如下:

select * from testyw--语文成绩表

select * from testsx--数学成绩表

对表进行查询:

左(外)连接查询结果:

select * from testyw y left join testsx s on(y.id=s.id)
--或者
select * from testyw y,testsx s where y.id=s.id(+)
--"(+)"所在位置的另一侧为连接的方向.
--左连接说明等号左侧的所有 记录均会被显示，无论其在右侧是否得到匹配。

右(外)连接查询结果:

select * from testyw y right join testsx s on(y.id=s.id)
--或者
select * from testyw y,testsx s where y.id(+)=s.id
--"(+)"所在位置的另一侧为连接的方向.
--右连接说明等号右侧的所有 记录均会被显示，无论其在左侧是否得到匹配。

全连接查询结果:

select * from testyw y full join testsx s on(y.id=s.id)
--记住,全连接可不能像左右连接一样左右带"(+)",这里只有一种写法.

6.Oracle使用外连接和自连接

经常早查询语句中用到这样的连接有点小问题，所以特意查了一下资料归纳一下：

外连接比较容易理解，

A、一般在某些分类编码中使用，例如你有一张员工信息表，其中的职位信息是个代码，而这个代码对应的含义在职位表中描述

B、信息分类存储，例如上述的员工信息表，其对应的每个月的工资情况在领一张表中，这个时候需要外连接进行表关联

外连接的关联字段一般在数据类型和名称上相同或者相似，你可以这么写 from a left join b on a.id = b.id

自连接比较特殊，一般反映记录的上下级关系，例如员工信息表中会有一个字段：上级领导，这个字段对应的上级其实也是一个员工，这个员工信息就是通过自连接进行查找的
自连接一般要求在表中有一个特殊的字段，连接时要注意例如 from a tab1 left join a tab2 on tab1.up_id = tab2.id