9. 子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询。
子查询的基本使用
- 子查询(内查询)在主查询之前一次执行完成。
- 子查询的结果被主查询(外查询)使用 。
-- 1.查询工资比'Abel'高的员工姓名和工资
-- 自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`
-- 子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
注意事项
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询,多行操作符对应多行子查询
子查询的分类
分类方式:
- 按内查询的结果返回一条还是多条记录,将子查询分为单行子查询 、多行子查询。
- 按内查询是否被执行多次,将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。
2. 单行子查询
单行比较操作符
操作符 | 含义 |
---|---|
= | 相等 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
<> | 不等于 |
-- 'Kimberely'的department_id为NULL,即子查询中的结果为NULL
-- 当使用=操作符时最终结果为NULL
SELECT first_name, department_id
FROM employees
WHERE department_id <=>
(SELECT department_id
FROM employees
WHERE first_name = 'Kimberely');
单行子查询中可以使用<=>对NULL进行判断
HAVING 中的子查询
-- 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
CASE中的子查询
-- 显式员工的employee_id,last_name和location。
-- 其中,若员工department_id与location_id为1800
-- 的department_id相同,则location为’Canada’,其余则为’USA’。
SELECT employee_id, last_name,
(CASE department_id WHEN
(SELECT department_id FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;
注意:
-
子查询中的空值问题
子查询不返回任何行 -
非法使用子查询
多行子查询使用单行比较符
3. 多行子查询
- 也称为集合比较子查询
- 内查询返回多行
- 使用多行比较操作符
多行比较操作符
-- 查询平均工资最低的部门信息
SELECT *FROM departments
WHERE department_id=(SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(SELECT AVG(salary) sal
FROM employees
GROUP BY department_id
ORDER BY sal
LIMIT 1));
-- #方式2:
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
)
注意:
空值问题
- IN中的null是无效的,不能得到对应数据,NULL的情况需要单独判断
- NOT IN的列表中不能包含NULL否则返回值为空。
4. 相关子查询
如果子查询的执行依赖于外部查询,通常情况下都是因为子查询中的表用到了外部的表,并进行了条件关联,因此每执行一次外部查询,子查询都要重新计算一次,这样的子查询就称之为 关联子查询 。
-- 查询员工中工资大于本部门平均工资的员工的
-- last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary>(SELECT AVG(salary)
FROM employees e2
WHERE e2.`department_id`=e1.`department_id`);
在 FROM 中使用子查询
-- 查询员工中工资大于本部门平均工资的员工的
-- last_name,salary和其department_id
SELECT last_name,salary,e1.department_id
FROM employees e1,(SELECT department_id,
AVG(salary) dept_avg_sal FROM employees
GROUP BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;
在ORDER BY 中使用子查询:
-- 查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (SELECT department_name
FROM departments d
WHERE e.`department_id` = d.`department_id`);
在SELECT中,除了GROUP BY 和 LIMIT之外,其他位置都可以声明子查询!
EXISTS 与 NOT EXISTS关键字
关联子查询通常也会和 EXISTS操作符一起来使用,用来检查在子查询中是否存在满足条件的行。
- 如果在子查询中不存在满足条件的行:
- 条件返回 FALSE
- 继续在子查询中查找
- 如果在子查询中存在满足条件的行:
- 不在子查询中继续查找
- 条件返回 TRUE
- NOT EXISTS关键字表示如果不存在某种条件,则返回TRUE,否则返回FALSE。
-- 查询公司管理者的employee_id,last_name,job_id,department_id信息
-- 方式一:自连接
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;
-- 方式二:
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees
);
-- 方式三:
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( SELECT *FROM employees e2
WHERE e2.manager_id =e1.employee_id);
相关更新
使用相关子查询依据一个表中的数据更新另一个表的数据。
-- 在employees中增加一个department_name字段,
-- 数据为员工对应的部门名称
# 1)
ALTER TABLE employees
ADD(department_name VARCHAR2(14));
# 2)
UPDATE employees e
SET department_name = (SELECT department_name
FROM departments d
WHERE e.department_id =
d.department_id);
相关删除
使用相关子查询依据一个表中的数据删除另一个表的数据。
-- 删除表employees中,其与emp_history表皆有的数据
DELETE FROM employees e
WHERE employee_id in(SELECT employee_id
FROM emp_history
WHERE employee_id = e.employee_id);
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)