oracle 多表查询

3.6多表查询
当查询的数据并不是来源一个表时，需要使用多表连接操作完成查询。多表连接查询通过表之间的关联字段，一次查询出多个表的数据。
3.6.1等值连接
等值连接也称为简单连接(Simple Joins)或者内连接(Inner Join)。通过等号来判断连接条件中的数据值是否相匹配，具体格式如下：
Select table1_name.fieldname，
table2_name.fieldname
from table1_name， table2_name
Where 条件句

【例3-8】建立雇员表，包括字段编号、姓名、岗位名称。建立职工薪水表包括职工号、姓名、工资。需要查询出编号、姓名、岗位名称、薪水信息。具体代码如下：
–第3章\ bdgl.sql

－－建立雇员表

create table EMPLOYEE
( id_1 varchar2(20)，
name_1 varchar2(25)，
station varchar2(20)
);

－－建立职工薪水表

create table SALARY
(
id_2 varchar2(20)，
name_2 varchar2(25)，
salary NUMBER(8，2)
);

――插入数据
insert into employee values(‘1’，‘lily’，‘工程师’);
insert into employee values(‘3’，‘lucy’，‘项目经理’);
insert into employee values(‘5’，‘william’，‘人事经理’);
insert into employee values(‘6’，‘demon’，‘行政人员’);
insert into employee values(‘7’，‘david’，‘人事专员’);
Commit;
insert into salary values(‘1’，‘lily’，5000);
insert into salary values(‘2’，‘tom’，8000);
insert into salary values(‘3’，‘lucy’，5500);
insert into salary values(‘4’，‘linda’，3500);
insert into salary values(‘5’，‘william’，6000);
Commit;

select a.id_1，a.name_1，a.station，b.salary
from employee a，salary b
where a.id_1=b.id_2 ;
–也可以使用关键字inner join
select a.id_1，a.name_1，a.station，b.salary
from employee a
inner join salary b
on a.id_1=b.id_2 ;

查询结果如图3-14所示。

图3-14使用内连接的查询结果

从查询结果上可以看出查询了雇员表和薪水表中编号相同的数据。

3.6.2外连接
(1)左外连接：使用关键字 left join，以左表为准，返回左表的所有数据，右表满足关联条件的数据会全部显示，否则用 null 值去填充。
【例3-9】使用左外连接查询雇员表编号、姓名、岗位名称、薪水信息，代码如下：
–第3章\bdgl.sql

select a.id_1，a.name_1，a.station，b.salary
from employee a
Left join salary b
on a.id_1=b.id_2 and a.name_1=b.name_2;
查询结果如图3-15所示。

图3-15使用左外连接的查询结果
该查询的主表是employee，查询记录数与employee 一致，职工号全部是employee 数据。
(2)右外连接：和左外连接相反。使用关键 right join，以右表为准，返回右表的所有数据，左表满足 on 条件的数据会全部显示，否则用 null 值去填充。
【例3-10】使用右连接查询出编号、姓名、岗位名称、薪水信息，代码如下：
–第3章\bdgl.sql

select a.id_1，a.name_1，a.station，b.salary
from employee a
right join salary b
on a.id_1=b.id_2 and a.name_1=b.name_2;

查询结果如图3-16所示。

图3-16使用右外连接的查询结果
(3)完全连接：通过关键字 full join 连接两张表，返回左表和右表的所有数据，并使用空值填充缺失的数据。
【例3-11】使用完全连接查询编号、姓名、岗位名称、薪水信息，代码如下：
–第3章\bdgl.sql
select a.id_1，a.name_1，b.id_2，b.name_2，a.station，b.salary
from employee a
full join salary b
on a.id_1=b.id_2 and a.name_1=b.name_2;

查询结果如图3-17所示。

图3-17使用完全连接的查询结果
最后结果为表a和表b的所有数据。

3.7表的集合运算
一个查询就是一个集合：查询的结果集一条记录就是一个元素。
集合运算是用来把两个或多个查询的结果集做并、交、查的集合运算，包含集合运算的查询称为复合查询。
Select 基本语法如下：
SELECT
column_1，
column_2，
…
FROM
table_name
WHERE
search_condition
ORDER BY
column_1，
column_2;
3.7.1常用集合运算方式的应用
(1)联合运算：联合运算实际是结果集1加上结果集2的纪录。
将两个表或多个表数据合并到一起，只有所选出的列数据类型匹配的查询才可以进行逻辑运算。
联合运算语法如下：
select 字段列表1 from 表1
UNION
select 字段列表2 from 表2
字段列表1 和字段列表2 数据类型一致，长度可以不一致，但字段个数必须一致。
在合并多个集合(表)的过程中，去掉表的重复记录。
(2)全联合运算：是从每个查询返回包括所有重复的结果。
注意：使用union all会比union的速度快，因为省去了去除重复记录的时间。
语法如下：
select 字段列表1 from 表1
UNION all
select 字段列表2 from 表2
(3)相交运算(intersect)，该运算不去重，默认升序。
该运算取两个集合的交集，该运算返回多个查询中所有相同的行。
语法如下：
select 字段列表1 from 表1
INTERSECT
select 字段列表2 from 表2
(4)MINUS：结果集1中有，结果集2中没有的纪录。
语法如下：
select 字段列表1 from 表1
MINUS
select 字段列表2 from 表2
使用该操作时，注意以下加点：
①　选择的列数必须相同；
②　所选列的数据类型必须有相同的数据类型；
③　列的名称不必相同。
【例3-12】建立职工表，包括员工编号、姓名、薪水、电话号码。建立职工性别表，包括员工编号、姓名、薪水、电话、性别，然后插入测试数据。
要求：取两个表的并集，有重复的记录也保留；
取两个表的并集，重复的记录需要去掉；
取两个表的交集；
取两个表的差集。
建表，插入数据，代码如下：
–第3章\bdgl.sql

–建立职工表
create table SM_EMP
( empid varchar2(30)，
name varchar2(30)，
salary NUMBER(6，2)，
telno CHAR(8)
);
–建立职工性别表
create table SM_EMP_SEX
( empid varchar2(30) not null，
name varchar2(30)，
salary NUMBER(6，2)，
telno CHAR(8)，
sex CHAR(2)
);
INSERT INTO sm_emp VALUES(‘0000000001’，‘张飞飞’，5500，'6678562 ');
INSERT INTO sm_emp VALUES(‘0000000002’，‘关庭’，4500，‘87825626’);
INSERT INTO sm_emp VALUES(‘0000000003’，‘孙海’，6200，‘87783617’);
Commit;

INSERT INTO sm_emp_sex VALUES(‘000000001’，‘张飞飞’，5500，'6678562 '，‘男’);
INSERT INTO sm_emp_sex VALUES(‘000000002’，‘关庭’，4500，‘87825626’，‘女’);
INSERT INTO sm_emp_sex VALUES(‘000000006’，‘王兴悦’，6200，‘87783617’，‘女’);
Commit;
1.取两个表的并集，有重复的记录保留，代码如下：
select a.empid，a.name，a.salary，a.telno from sm_emp a
union all
select b.empid，b.name ，b.salary，b.telno from sm_emp_sex b；
查询结果如图3-18所示。

图3-18职工表和性别表取并集的查询结果

2.取两个表的并合，重复的记录需要删除，代码如下：
select a.empid，name from sm_emp a
union
select b.empid，b.name from sm_emp_sex b;
查询结果如如图3-19所示。

图3-19职工表和性别表取并集并去重的查询结果
3.取两个表的交集，代码如下：
select a.empid，name，salary，a.telno from sm_emp a
INTERSECT
select b.empid，b.name ，salary，b.telno from sm_emp_sex b;
查询结果如图3-20下：

图3-20职工表和性别表取并交集的查询结果
4.取两个表的差集，代码如下：
select a.empid，name，salary，a.telno from sm_emp a
MINUS
select b.empid，b.name ，salary，b.telno from sm_emp_sex b
查询结果如图3-21所示。

图3-21职工表和性别表取差集的查询结果