多表查询

2
1

programming
1
Oracle SQL开发基础

2
2

programming
课程结构

内容

课时（H）

第一章 Oracle数据库基础

2.5

第二章 编写简单的查询语句

1.5

第三章 限制数据和对数据排序

2

第四章 单行函数

4

第五章 多表查询

4

第六章 分组函数

3

第七章 子查询

4

第八章 数据操作与事务控制

4

第九章 表和约束

4

第十章 其他数据库对象

4

 

2
3

programming
第五章 多表查询

目标:

本章旨在向学员介绍：

1)等值连接和不等值连接

2)外连接

3)ANSI SQL ：1999标准的连接
语句

4）Union 和Union All

 

时间： 4学时
教学方法：讲授ppt＋上机练习
2
4

programming
本章要点
.多表连接的定义
.笛卡尔积
.等值连接
.不等值连接
.外连接
.ANSI SQL：1999标准的连接语句
.Union，Union All(集合运算符)

2
5

programming
第五章 多表查询

多表查询:

5.1ORACLE自有连接方法

5.2ANSI SQL：1999标准的连接语法

5.3集合运算

2
6

programming
5.1ORACLE自有连接方法

.多表连接主要分为
–笛卡尔积
–等值连接
–不等值连接
–外连接
–自连接

 

2
7

programming
5.1.1笛卡尔积
.笛卡尔积是把表中所有的记录作乘积操作，生成大量的结果，
而通常结果中可用的值有限。笛卡尔积出现的原因多种多样，
通常是由于连接条件缺失造成的。

…
SELECT last_name, job_id, department_name

FROM employees, departments;

2
8

programming
5.1.2等值连接基本语句 1/3

.等值连接又称简单连接或内连接。就是当两个表的公共字段相
等的时候把两个表连接在一起。公共字段是两个表中有相同含
义的列。

EMPLOYEES
DEPARTMENTS
Foreign key Primary key
…

…

2
9

programming
5.1.2等值连接基本语句 2/3
.等值连接的语法结构
–在 WHERE 子句中写连接条件
–当多个表中有重名列时，必须在列的名字前加上表名作为
前缀

 

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
2
10

programming
5.1.2等值连接基本语句 3/3
SELECT employees.last_name, employees.job_id,
employees.department_id,departments.department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
…

 

等值连接的示例

2
11

programming
5.1.3等值连接中的记录筛选
.多表连接中，记录筛选语句同样写在WHERE语句中，用逻辑
AND和连接判断语句写在一起。

SELECT employees.last_name, employees.job_id,
employees.department_id,departments.department_name
FROM employees, departments
WHERE employees.department_id=departments.department_id
AND job_id LIKE '%MAN%';
2
12

programming
5.1.4表别名的使用 1/2
.等值连接表别名示例

SELECT e.last_name, e.job_id, e.department_id,
d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id

AND job_id LIKE '%MAN%';

2
13

programming
5.1.4表别名的使用 2/2

.关于表别名需要注意以下几点：
–表别名长度不超过30个字符;
–表别名定义在FROM子句中；
–如果已经定义了表别名，那么只能使用表别名而不能使用
原表名；
–表别名的有效范围只是当前语句。

.SQL语句的书写顺序是：
–SELECT FROM WHERE ORDER BY

.而实际的执行顺序是：
–FROM WHERE SELECT ORDER BY

 

2
14

programming
5.1.5两表以上的多表连接
.查找特定城市员工信息。

SELECT e.last_name, e.job_id, e.department_id,
d.department_name,l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.city IN ('Southlake','Oxford');
2
15

programming
5.1.6不等值连接 1/3
.除了等号之外，在表连接语句中还可以使用其它的运算符。这
种使用除等号之外运算符的连接语句被称为不等值连接。
.使用不等值连接查询可以查询两个表中具有非等值关系的数据。
操作符可以是比较运算符，也可以是between···and 或者是in、
like。

2
16

programming
5.1.6不等值连接 2/3

DESC salgrades;
SELECT *
FROM salgrades;
2
17

programming
5.1.6不等值连接 3/3
SELECT e.last_name, e.job_id, e.salary, s.grade_level
FROM employees e, salgrades s
WHERE e.salary BETWEEN s.lowest_salary AND
s.highest_salary AND e.job_id in('IT_PROG','SA_REP')
ORDER BY s.grade_level;
2
18

programming
5.1.7外连接 1/4
.为了查找到所有记录，包括没有匹配的记录，需要用外连接语句来实
现。
.右外连接：以右表为基准，右表中的每个记录都必须显示，即使左表
中没有与之相匹配的记录。
.左外连接：以左表为基准，左表中的每个记录都必须显示，即使右表
没有与之相匹配的记录。
.注意：+放在没有与之相匹配表的那一端。

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column(+) = table2.column;

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column = table2.column(+);

2
19

programming
5.1.7外连接 2/4
EMPLOYEES
DEPARTMENTS
…

两个员工不属于任何一个部门

2
20

programming
5.1.7外连接 3/4

.例5-8 所有部门信息，不管部门是否有员工。

…
SELECT e.last_name, e.job_id, e.department_id,
d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;
2
21

programming
5.1.7外连接 4/4

.例5-9 所有员工信息，不管员工是否有部门。

…

 

SELECT e.last_name, e.job_id, e.department_id,
d.department_name

FROM employees e, departments d

WHERE e.department_id= d.department_id(+);

2
22

programming
第五章 多表查询
多表查询:
5.1ORACLE自有连接方法
5.2ANSI SQL：1999标准的连接语法
5.3集合运算
2
23

programming
5.2ANSI SQL：1999标准的连接语法 1/2

.Oracle9i除了Oracle自己的连接语法外，同时支持美国国家
标准协会（ANSI）的SQL：1999标准的连接语法。

SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)];
2
24

programming
5.2ANSI SQL：1999标准的连接语法 2/2

.CROSS JOIN：交叉连接，生成笛卡尔积；
.NATURAL JOIN：自然连接；
.USING (column_name)：USING子句，通过名字来具体指定
连接
.JOIN table2

 ON (table1.column_name = table2.column_name)：等值连
接语句；

.[LEFT|RIGHT|FULL OUTER JOIN：左外连接|右外连接|全外
连接。

2
25

programming
5.2.1交叉连接
.交叉连接子句（CROSS JOIN）是在SQL99标准中，为了生成
笛卡尔积而设计的。

SELECT last_name, job_id, department_name

FROM employees

CROSS JOIN departments;

…

2
26

programming
5.2.2自然连接
.NATURAL JOIN是SQL99中新增语句，连接条件是两个表中所
有的值和数据类型都相同的同名列。如果仅列名相同而数据类
型不同，则报错。

SELECT department_id, department_name, city
FROM departments
NATURAL JOIN locations;
2
27

programming
5.2.3USING子句 1/2
.USING (column_name)子句也是SQL99新增子句，可以较灵
活的完成在多表连接，多列列名相同时，使用其中的一列同名
列连接，而不需写连接条件的功能。
. USING子句和NATURAL JOIN不能在一条语句中同时书写。

2
28

programming
5.2.3USING子句 2/2
…
SELECT last_name, job_id, department_name
FROM employees
JOIN departments
USING(department_id);
2
29

programming
5.2.4在ON子句中写连接条件
.例

SELECT e.last_name, e.job_id,
e.department_id,d.department_name
FROM employees e
JOIN departments d
ON （e.department_id = d.department_id）;
…

 

2
30

programming
5.2.5ANSI SQL：1999中实现两表以上连接
.例5-14 查找特定城市员工信息。

SELECT e.last_name, e.job_id, e.department_id,
d.department_name,l.city
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE l.city IN ('Southlake','Oxford');
2
31

programming
5.2.6左外连接
.在LEFT OUTER JOIN中，会返回所有左边表中的行，即使在
右边的表中没有可对应的列值。
.例5-15 所有员工信息，不管员工是否有部门。

SELECT e.last_name, e.job_id, e.department_id,
d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id= d.department_id;
2
32

programming
5.2.7右外连接

.RIGHT OUTER JOIN，会返回所有右边表中的行，即使在左
边的表中没有可对应的列值。
.例5-16 所有部门信息，不管部门是否有员工。

SELECT e.last_name, e.job_id, e.department_id,
d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id= d.department_id;
2
33

programming
5.2.8全外连接
.全外连接（FULL OUTER JOIN）主要功能是返回两表连接中等
值连接结果，及两个表中所有等值连接失败的记录。
.所有信息，不管部门是否有员工，也不管员工是否有部门。

SELECT e.last_name, e.job_id, e.department_id,
d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id= d.department_id;
2
34

programming
第五章 多表查询
多表查询:

5.1ORACLE自有连接方法

5.2ANSI SQL：1999标准的连接语法

5.3集合运算

2
35

programming
5.3.1Union 操作符 1/3
UNION
操作符返回两个查询的结果集的并集（去掉重复值
后的结果）
UNION 操作符返回两个查询的结果集的并集（去掉重复值
后的结果）
2
36

programming
5.3.1Union 操作符 2/3
.UNION (联合)运算
–UNION 运算返回所有由任一查询选择的行。用 UNION 运算从
多表返回所有行，但除去任何重复的行。
–原则
.被选择的列数和列的数据类型必须是与所有用在查询中的
SELECT 语句一致。列的名字不必相同。
.联合运算在所有被选择的列上进行。
.如果多个查询结果都有NULL值，整个结果中只包含一个
NULL值
.IN 运算有比 UNION 运算高的优先级。
.在默认情况下，输出以 SELECT 子句的第一列的升序排序。
.每个查询不能包含自己的Order by语句，只能在联合之后使
用Order by 。

 

 

 

2
37

programming
5.3.1Union 操作符 3/3

.列出工资大于15000或者部门在60，90的员工编号，
First_name,salary,department_id

Select employee_id,first_name,salary,department_id
From employees
Where salary>15000
Union
Select employee_id,first_name,salary,department_id
From employees
Where department_id in (60,90)
2
38

programming
5.3.2Union ALL 操作符 1/4
A

B
UNION ALL
操作符返回两个查询的结果集的并集以及两
个结果集的重复部分（不去掉重复值的结果）
UNION ALL 操作符返回两个查询的结果集的并集以及两
个结果集的重复部分（不去掉重复值的结果）
2
39

programming
5.3.2Union ALL 操作符 2/4

.完全联合 (UNION ALL) 运算
–原则
.和联合不同，重复的行不被过滤，并且默认情况下输出
不排序。
.如果每个查询结果中都有NULL值，也不被去掉。

–注意：使用UNION ALL会比UNION的速度快，因为省去了
去掉重复记录的时间。

 

2
40

programming
5.3.2Union ALL 操作符 3/4
.列出工资大于15000或者部门在60，90的员工编号，
First_name,salary,department_id

Select employee_id,first_name,salary,department_id

From employees

Where salary>15000

Union all

Select employee_id,first_name,salary,department_id

From employees

Where department_id in (60,90)

2
41

programming
5.3.2Union ALL 操作符 4/4
.列出部门信息和部门的经理信息

select manager_id,'department:',department_name
from department
where manager_id is not null
union all
select manager_id,'manager:',last_name
from department d,employees e
where d.manager_id=e.employee_id
order by 1,2
2
42

programming
本章小结
.本章讲述了什么是多表连接，以及多表连接的几种类型，通过
例子说明了以前的多表连接语法和新的Sql99标准中的连接语法
的差异。
.笛卡尔积
.等价连接
.不等价连接
.外连接
.ANSI SQL：1999标准的连接语句
.Union操作符
.Union All操作符

2
43

programming
练习

.1.查询员工的编号，姓名，以及部门名称(分别使用Oracle语法，
自然连接，using子句，on子句)。
.2.查询部门名称为Shipping的员工的编号、姓名及所从事的工
作。
.3.查询员工的编号，姓名，以及部门名称，包括没有员工的部
门。
.4.查询员工的编号，姓名，以及部门名称，包括不属于任何部
门的员工。

2
44

programming
谢谢