第08章_子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询。
1. 子查询介绍
1.1 子查询的基本使用
-
子查询的基本语法结构:
SELECT select_list FROM tableA WHERE expr operator (SELECT select_list FROM tableB);
-
子查询(内查询)在主查询之前一次执行完成。
-
子查询的结果被主查询(外查询)使用。
-
注意事项
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询,多行操作符对应多行子查询
1.2 子查询的分类
-
分类方式1:按照内查询的结果返回一条还是多条记录,将子查询分为 单行子查询、多行子查询。
-
分类方式2:按照内查询是否被执行多次,将子查询划分为 相关(或关联)子查询 和 不相关(或非关联)子查询。
子查询从数据表中查询了数据结果,如果这个数据结果只执行一次,然后这个数据结果作为主查询的条件进行执行,这样的子查询称为不相关子查询。
如果子查询需要执行多次,即采用循环的方式,先从外部查询开始,每次都传入子查询进行查询,然后再将结果反馈给外部,这种嵌套的执行方式就称为相关子查询。
2. 单行子查询
2.1 单行比较操作符
2.2 代码示例
-- 查询工资大于id为149员工工资的员工信息
SELECT name
FROM emp
WHERE salary > (
SELECT salary
FROM emp
WHERE id = 149
);
2.3 HAVING中的子查询
- 首先执行子查询。
- 向主查询中的HAVING子句返回结果
-- 查询最低工资大于50号部门最低工资的部门ID和其最低工资
SELECT department_id, MIN(salary)
FROM emp
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM emp
WHERE department_id = 50
);
2.4 CASE中的子查询
在CASE表达式中使用单列子查询:
-- 显示员工的id、name和location。其中,若员工department_id与loction_id为1800的department_id相同,则location为“C anada,其余则为“USA”。
SELECT id, name,
(CASE department_id
WHEN
(SELECT department_id
FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM emp;
2.5 子查询中的空值问题
SELECT name, id
FROM emp
WHERE id =
(SELECT id
FROM emp
WHERE name = 'abc');
-- no rows selected
子查询不返回任何行
2.6 非法使用子查询
SELECT id, name
FROM emp
WHERE salary =
(SELECT MIN(salary)
FROM emp
GROUP BY department_id);
-- 错误:Subquery returns more than 1 row
多行子查询使用单行比较符
3. 多行子查询
- 也称为集合比较子查询
- 内查询返回多行
- 使用多行比较操作符
3.1 多行比较操作符
体会 ANY 和 ALL 的区别
3.2 代码示例
SELECT id, name, job_id
FROM emp
WHERE job_id <> 'IT'
AND salary < ANY (
SELECT salary
FORM emp
WHERE job_id = 'IT'
);
SELECT id, name, job_id
FROM emp
WHERE job_id <> 'IT'
AND salary < ALL (
SELECT salary
FORM emp
WHERE job_id = 'IT'
);
3.3 空值问题
SELECT name, id
FROM emp
WHERE id NOT IN
(SELECT id
FROM emp
-- WHERE id IS NOT NULL
);
-- no rows selected
4. 相关子查询
4.1 相关子查询执行流程
如果子查询的执行依赖于外部查询,通常情况下都是因为子查询中的表用到了外部的表,并进行了条件关联,因此每执行一次外部查询,子查询都要重新计算一次,这样的子查询就称之为关联子查询。
相关子查询按照一行接一行的顺序执行,主查询的每一行都执行一次子查询。
说明:子查询中使用主查询中的列
4.2 代码示例
-- 查询员工中工资大于本部门平均工资的员工的name,salary和其department_id
-- 方式1:相关子查询
SELECT name, salary, separtment_id
FROM emp e1
WHERE salary > (SELECT AVG(salary)
FROM emp
WHERE department_id = e1.department_id);
-- 方式2:在FROM中使用子查询
SELECT name, salary, e1.department_id
FROM emp e1, (SELECT department_id, AVG(salary) dept_avg_sal FROM emp GROUP BY department_id) e2
WHERE e1.department_id = e2.department_id
AND e2.dept_avg_sal < e1.salary;
from型的子查询:子查询是作为from的一部分,子查询要用()引起来,并且要给这个子查询取别名,把它当成一张“临时的虚拟的表”来使用。
在ORDRE BY 中使用子查询:
-- 查询员工的id,salary,按照department_name排序
SELECT id, salary
FROM emp e
GROUP BY (
SELECT departmen_name
FROM departments d
WHERE e.id = d.id
) ASC;
4.3 EXISTS 与 NOT EXISTS关键字
- 关联子查询通常也会和EXISTS操作符一起来使用,用来检查在子查询中是否存在满足条件的行。
- 如果在子查询中不存在满足条件的行:
- 条件返回FALSE
- 继续在子查询中查找
- 如果在子查询中存在满足条件的行:
- 不在子查询中继续查找
- 条件返回TRUE
- NOT EXISTS关键字表示如果不存在某种条件,则返回TRUE,否则返回FALSE。
-- 查询departs表中,存在于emp表中的depart_id和depart_name
SELECT depart_id, depart_name
FROM departs d
WHERE EXISTS (
SELECT *
FROM emp e
WHERE d.depart_id = e.depart_id
);
4.4 相关更新
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
使用相关子查询依据一个表中的数据更新另一个表中的数据。
4.5 相关删除
DELETE FROM table1 alias1
WHERE column operator (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);