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 IN
  • IN()等价于=ANY() NOT IN()等价于<>ALL()
• ANY/SOME
• ALL

返回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就传递到了子查询中。

该子查询可以理解为：

1. 先获取外层查询的结果集
2. 然后对结果集的每条记录进行遍历，将对应的参数填入到子查询中得到单条记录的结果拼接到新列中

五、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语句

1. 查询男神信息
2. 筛选条件：他没有女票
   等价于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完成相同的任务：

1. 查询男神信息
2. 筛选条件：他没有女票
   等价于当前男生的id不在beauty 表中boyfriend_id字段的集合中

SELECT * 
FROM boys b
WHERE b.id NOT IN(
	SELECT boyfriend_id
	FROM beauty
);