子查询

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）多行子查询

4）相关子查询

 

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

2
4

programming
本章要点

.子查询语法介绍
.单行子查询
.多行子查询
.相关子查询
.EXISTS和NOT EXISTS操作符

2
5

programming
第七章 子查询

子查询:

7.1子查询的概念

7.2单行子查询

7.3多行子查询

7.4FROM语句中子查询

7.5相关子查询

2
6

programming
7.1.1子查询的基本语法 1/2
.子查询的语法结构：
.括号内的查询叫做子查询（Subquery）或者内部查询（Inner
Query），外面的查询叫做主查询（Main query）或外部查询
（Outer query）。

SELECT 查询列
FROM 表名
WHERE 列名 操作符
(SELECT 查询列
FROM 表名);
2
7

programming
7.1.1子查询的基本语法 2/2

.例7-1 和Chen员工在相同部门的员工信息。
.关于子查询，需要注意以下几点：
–子查询需要写在括号中；
–子查询需要写在运算符的右端；
–子查询可以写在WHERE，HAVING，FROM子句中；
–子查询中通常不写ORDER BY子句。

 

SELECT last_name, job_id, salary, department_id

FROM employees

WHERE department_id = (SELECT department_id

FROM employees

WHERE last_name = 'Chen');

2
8

programming
7.1.2子查询的种类

 单行子查询(单列)
主查询
子查询

返回
返回 CLERK
CLERK

多行子查询(单列)

CLERK
MANAGER
CLERK

MANAGER

主查询
子查询 返回
返回
多列子查询

CLERK 7900
MANAGER 7698
CLERK 7900
MANAGER 7698
主查询

子查询

返回
返回

2
9

programming
第七章 子查询
子查询:
7.1子查询的概念
7.2单行子查询
7.3多行子查询
7.4FROM语句中子查询
7.5相关子查询
2
10

programming
7.2单行子查询

.单行子查询，子查询返回的记录有且只有一条。单行子查询要
求使用单行操作符，即:
–> 大于
–>= 大于等于
–< 小于
–<= 小于等于
–= 等于
–<> 不等于

 

2
11

programming
7.2.1WHERE子句中单行子查询

.例7-2 WHERE子句中单行子查询示例。

SELECT last_name, job_id, salary, department_id

FROM employees

WHERE department_id =

 (SELECT department_id

 FROM employees

 WHERE last_name = 'Chen')

AND salary >

 (SELECT salary

 FROM employees

 WHERE last_name = 'Chen');

2
12

programming
7.2.2HAVING子句中单行子查询
.除了在WHERE子句中外，也可以在HAVING子句中书写子查询。
.例7-4 员工人数高于各部门平均人数。

SELECT department_id, COUNT(employee_id)

FROM employees

GROUP BY department_id

HAVING COUNT(employee_id) >

 (SELECT AVG(COUNT(employee_id))

 FROM employees

 GROUP BY department_id);

2
13

programming
第七章 子查询
子查询:

7.1子查询的概念

7.2单行子查询

7.3多行子查询

7.4FROM语句中子查询

7.5相关子查询

2
14

programming
7.3多行子查询
.多行子查询，子查询返回记录的条数可以是一条或多条。多行
子查询需要使用多行操作符。常用的多行操作符包括：
–IN
–ANY
–ALL

.IN操作符和以前介绍的功能一致，判断是否与子查询的任意一
个返回值相同。返回的结果可以是一条或多条。

2
15

programming
7.3.1IN、ANY、ALL的使用 1/5
.返回结果

ERROR at line 4:

ORA-01427: single-row subquery returns more than
one row

SELECT employee_id, last_name

FROM employees

WHERE salary =

 (SELECT MIN(salary)

 FROM employees

 GROUP BY department_id);

2
16

programming
7.3.1IN、ANY、ALL的使用 2/5
.例7-6 多行子查询示例——IN操作符。

SELECT a.last_name, a.salary
FROM employees a
WHERE a.employee_id IN
(SELECT b.manager_id
FROM employees b);
2
17

programming
7.3.1IN、ANY、ALL的使用 3/5
.ANY：表示任意的。
–< ANY 比子查询返回的任意一个结果小即可，即小于返回结
果的最大值。
–= ANY 和子查询中任意一个结果相等即可，相当于IN。
–> ANY比子查询返回的任意一个结果大即可，即大于返回结
果的最小值。

.ALL：表示所有的。
–< ALL 比子查询返回的所有的结果都小，即小于返回结果的
最小值。
–> ALL比子查询返回的所有的结果都大，即大于返回结果的
最大值。
–= ALL 无意义，逻辑上也不成立。

 

2
18

programming
7.3.1IN、ANY、ALL的使用 4/5

.例7-7 ANY操作符。

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary > ANY
(SELECT salary
FROM employees
WHERE department_id = 80)
AND department_id <> 80;
2
19

programming
7.3.1IN、ANY、ALL的使用 5/5
.例7-8 ALL操作符。

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary > ALL
(SELECT salary
FROM employees
WHERE department_id = 80)
AND department_id <> 80;
2
20

programming
7.3.2多列子查询
显示雇员的细节，要求查出和101或104被同一个经理管理并且
工作在同一个部门的员工，但不包括101和104本人。

SELECT employee_id,department_id,manager_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id FROM
employees
WHERE employee_id IN (101,104))
AND employee_id NOT IN (101,104);
2
21

programming
7.3.3子查询中空值问题
.例7-14 子查询空值示例。

 

.出现这种情况的原因有两个：
–子查询中返回值中包含有空值；
–NOT IN操作符对空值不忽略。

.NOT IN操作符相当于<> ALL，即除了列表值的所有值，就包括
了空值NULL，结果即为空。

SELECT a.last_name, a.salary FROM employees a
WHERE a.employee_id NOT IN
(SELECT b.manager_id FROM employees b);
返回结果：未选定行
2
22

programming
第七章 子查询
子查询:
7.1子查询的概念
7.2单行子查询
7.3多行子查询
7.4FROM语句中子查询
7.5相关子查询
2
23

programming
7.4FROM语句中子查询

.例7-9 FROM子句查询示例——分组判断。找出比员工本职位
（job_id）平均工资高的员工信息。

SELECT e.last_name, e.salary, e.job_id, j.avgsal
FROM employees e, (SELECT job_id,
AVG(salary) avgsal
FROM employees
GROUP BY job_id) j
WHERE e.job_id = j.job_id
AND e.salary > j.avgsal;
2
24

programming
第七章 子查询
子查询:
7.1子查询的概念
7.2单行子查询
7.3多行子查询
7.4FROM语句中子查询
7.5相关子查询
2
25

programming
7.5相关子查询 1/2

 当子查询从一个在父查询涉及的表中引用一个列时，Oracle服务器
执行相关子查询。对于由父查询处理的每一行相关子查询都执行一次。
相关子查询被用于 row-by-row 处理。对父查询的每一行，每个子查
询被执行一次
GET
取来自父查询的候选行
EXECUTE

用候选行值执行内查询

USE
用内查询的值确认或取消候选行
2
26

programming
7.5相关子查询 2/2
.相关子查询中，内部查询需引用外部查询的列，进行交互判断。
相关子查询的执行方式是一行行操作。外部查询每执行一行操
作，内部查询都要执行一次。
.例7-16 公司经理希望找到比本职位平均工资高的员工的信息，
可通过分组判断实现。

SELECT last_name, salary, job_id

FROM employees e

WHERE salary >(SELECT AVG(salary)

 FROM employees

 WHERE job_id = e.job_id);

2
27

programming
7.5.1EXISTS和NOT EXISTS操作符 1/3
.相关子查询还可使用EXISTS和NOT EXISTS操作符。
.EXISTS判断存在与否。具体操作如下：
–子查询中如果有记录找到，子查询语句不会继续执行，返回
值为TRUE；
–子查询中如果到表的末尾也没有记录找到，返回值为FALSE。

.EXISTS子查询并没有确切记录返回，只判断是否有记录。而且
只要找到相关记录，子查询就不需要再执行，然后再进行下面
的操作。这样大大提高了语句的执行效率。
.NOT EXISTS正好相反，判断子查询是否没有返回值。如果没
有返回值，表达式为真，如果找到一条返回值，则为假。

2
28

programming
7.5.1EXISTS和NOT EXISTS操作符 2/3

.例7-17 查找公司中的经理。
.因为EXISTS子句中，并没有确切记录返回，只返回真或假。所
以’1’只是占位用，无实际意义。

SELECT last_name, job_id, salary, department_id
FROM employees e
WHERE EXISTS (SELECT '1'
FROM employees
WHERE manager_id = e.employee_id);
2
29

programming
7.5.1EXISTS和NOT EXISTS操作符 3/3
.例7-18 查找公司中不是经理的员工。
.NOT EXISTS操作符因为运算方法与NOT IN不同，只会返回
TRUE或FALSE，不会返回空值，所以不需要考虑子查询去除
空值的问题。

SELECT last_name, job_id, salary, department_id
FROM employees e
WHERE NOT EXISTS (SELECT '1'
FROM employees
WHERE manager_id = e.employee_id);
2
30

programming
本章小结
.子查询的基本介绍
.单行子查询
.多行子查询
.FROM语句中子查询
.子查询中空值问题
.相关子查询
.EXISTS和NOT EXISTS操作符

2
31

programming
练习
.1.查询工资高于编号为113的员工工资，并且和102号员工从事
相同工作的员工的编号、姓名及工资。
.2.查询工资最高的员工姓名和工资。
.3.查询部门最低工资高于100号部门最低工资的部门的编号、
名称及部门最低工资。
.4.查询员工工资为其部门最低工资的员工的编号和姓名

 及工资。

.5.显示经理是KING的员工姓名，工资。
.6.显示比员工‘Abel’参加工作时间晚的员工姓名，工资，参加
工作时间。

2
32

programming
谢谢