SQL语句（五）子查询
一、子查询含义
出现在其他语句中的select语句，称为子查询或内查询。
二、子查询分类
按子查询的位置分
| 位置 | 支持的查询 |
|---|---|
SELECT后面 |
支持标量子查询 |
FROM后面 |
支持表子查询 |
WHERE或HAVING后面 |
支持标量子查询、列子查询和行子查询 |
EXISTS后面 |
支持表子查询 |
按结果集的行列数不同分
| 分类类型 | 结果集行数 |
|---|---|
| 标量子查询 | 一行一列 |
| 列子查询 | 一列多行 |
| 行子查询 | 一行多列 |
| 表子查询 | 多行多列 |
三、WHERE后面的子查询
1. 标量子查询
tips: 单行操作符（> < =等）只能搭配标量子查询
查询工资比Abel高的所有员工信息
①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel';
结果为单行单列：
②查询员工的信息，满足salary>①
查询公司工资最少的员工的last_name, job_id, salary：
①查询MIN(salary)
SELECT MIN(salary)
FROM employees;
②查询符号这项工资的员工，salary=①
SELECT last_name, job_id, salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
查询job_id与141号员工相同，salary比143号员工多的员工的姓名，job_id和工资
①141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id=141;
②143号员工的salary
SELECT salary
FROM employees
WHERE employee_id=143;
③ 结果集映射到last_name, job_id, salary，且满足条件 job_id=①和salary>②
SELECT last_name, job_id, salary
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id=141
)AND salary>(
SELECT salary
FROM employees
WHERE employee_id=143
);
2. 列子查询（多行子查询）
使用多行操作符：
IN/NOT ININ()等价于=ANY()NOT IN()等价于<>ALL()
ANY/SOMEALL
返回location_id是1400或1700的部门中的所有员工姓名
使用联表查询操作得到结果：
SELECT last_name
FROM departments d INNER JOIN employees e
ON d.department_id = e.department_id
WHERE d.location_id IN (1400, 1700);
使用列子查询得到结果：
①查询location_id是1400或1700的所有部门的编号
SELECT department_id
FROM departments
WHERE location_id IN (1400, 1700);
②查询满足部门编号在列表内的员工的姓名
SELECT last_name
FROM employees
WHERE department_id IN(
SELECT department_id
FROM departments
WHERE location_id IN (1400, 1700)
);
查询其他工种中的
比job_id为IT_PROG工种的任一员工工资低的
员工的：工号，姓名
①获取IT_PROG工种所有工资
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
②查询其他部门中符合条件salary<min(①)的员工信息
SELECT job_id, last_name
FROM employees
WHERE salary < ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id!='IT_PROG';
3. 行子查询（结果为一行多列或多行多列）
查询员工编号最小而且工资最高的员工信息
①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;
②查询员工的最高工资
SELECT MAX(salary)
FROM employees;
③查询符合employee_id=①，salary=②的员工
SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
)AND salary=(
SELECT MAX(salary)
FROM employees
);
使用行子查询的等价写法：
SELECT *
FROM employees
WHERE (employee_id, salary) = (
SELECT MIN(employee_id), MAX(salary)
FROM employees
);
四 、SELECT后面的子查询
查询每个部门的员工个数
使用联表查询操作：
SELECT department_name 部门名, COUNT(1) 员工数量
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
GROUP BY department_name
使用子查询：
SELECT department_id, department_name,(
SELECT COUNT(1)
FROM employees e
WHERE e.department_id=d.department_id
) 对应员工数
FROM departments d;
在使用子查询中，外部的表名的别名可以传递到子查询中，例如上面的departments 表的别名d就传递到了子查询中。
该子查询可以理解为：
- 先获取
外层查询的结果集 - 然后对结果集的
每条记录进行遍历，将对应的参数填入到子查询中得到单条记录的结果拼接到新列中
五、FROM后面的子查询
查询每个部门的平均工资的工资等级
①查询每个部门的平均工资
SELECT AVG(salary) ag,department_id
FROM employees e
WHERE e.department_id IS NOT NULL
GROUP BY department_id;
②查询对应的工资等级,连接①的结果集和job_grades表
SELECT department_id, ag, grade_level
FROM (
SELECT AVG(salary) ag,department_id
FROM employees e
WHERE e.department_id IS NOT NULL
GROUP BY department_id
) ag_dep INNER JOIN job_grades j
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
即可以将子查询的结果集作为一张表用于联表查询操作
六、EXISTS后面的子查询
EXISTS语句的作用
EXISTS语句后的括号中填入一个子查询语句，返回的结果为false(0)或true(1)，分别代表查询结果是否为空。
例：
SELECT EXISTS(SELECT employee_id FROM employees);
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=30000);
简单应用
查询有员工的部门名：
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE e.department_id = d.department_id
);
这里等价于用IN
SELECT department_name
FROM departments d
WHERE d.department_id IN(
SELECT DISTINCT department_id
FROM employees
);
查询没有女票的男神信息
使用EXISTS语句
- 查询
男神信息 - 筛选条件：他
没有女票
等价于beauty表中没有boyfriend_id为当前男生的记录
等价于beauty表中boyfriend_id为当前男生的id的记录不存在
SELECT *
FROM boys bo
WHERE NOT EXISTS(
SELECT *
FROM beauty b
WHERE bo.id=b.boyfriend_id
);
使用IN完成相同的任务：
- 查询
男神信息 - 筛选条件：他
没有女票
等价于当前男生的id不在beauty表中boyfriend_id字段的集合中
SELECT *
FROM boys b
WHERE b.id NOT IN(
SELECT boyfriend_id
FROM beauty
);
