子查询（嵌套查询）——MySQL

子查询（嵌套查询）

出现在其他语句中的select语句被称为子查询

按位置分类：

• select后面
• from后面
• where或having后面
• exists后面

按结果级的行数不同分类：

• 标量子查询 (结果集有一行一列)
• 列子查询 (结果集只有一列多行)
• 行子查询 (结果集只有一行多列)
• 表子查询 (结果集一般为多行多列)

where或having后面
标量子查询
列子查询
行子查询
特点：

1. 子查询都放在小括号内，结尾不用加 ;
2. 子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3. 子查询优先于主查询执行，主查询使用了子查询的执行结果

子查询根据查询结果的行数不同分为以下两类：单行子查询和多行子查询

1. 单行子查询
   结果集只有一行
   一般搭配单行操作符使用：> < = <> >= <=
   非法使用子查询的情况：

   1. 子查询的结果为一组值
   2. 子查询的结果为空

2. 多行子查询
   结果集有多行
   一般搭配多行操作符使用：any、all、in、not in
   in：属于子查询结果中的任意一个就行
   any和all:往往可以用其他查询代替

标准子查询

1. 谁的工资比abel高?
   1查询Abel的工资
   SELECT salary
FROM employees
WHERE last_name = 'Abel'

   2查询员工的信息，满足 salary>1结果
   SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
   把1的结果放在2的小括号内,括号内就是子查询。

自查行中用到分组函数。
2. 返回公司工资最少的员工的last_name,job_id和salary

1. 查询公司的 最低工资
   SELECT MIN(salary)
FROM employees

2. 查询last_name,job_id和salary，要求 salary=1
   SELECT last_name,job_id,salary
FROM employees
WHERE salary=
(
SELECT MIN(salary)
FROM employees
);

一个查询里面可以放两个子查询。
3. 返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资

SELECT last_name,job_id,salary
FROM employees
WHERE job_id =
(
SELECT job_id
FROM employees
WHERE employee_id = 141
) 查询141号员工的job_id
AND salary>
(
SELECT salary
FROM employees
WHERE employee_id = 143
);查询143号员工的salary

4. 查询最低工资大于50号部门最低工资的部门id和其最低工资

   1. 查询50号部门的最低工资
      SELECT MIN(salary)
FROM employees
WHERE department_id = 50

   2. 查询每个部门的最低工资

      SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id

   3. 在②基础上筛选，满足min(salary)>①
      SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>
(
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);

列子查询（多行子查询）

• 返回多行

• 使用多行比较操作符

  操作符	含义
  in/out in	等于列表中的任意一个
  not in	不在指定的集合范围内
  any/some	和子查询返回的某个值比较，有任意一个满足即可
  all	和子查询返回的所有值比较，必须全部满足

1. 案例: 查询 “销售部” 和 “市场部” 的所有员工信息(in)

分解为以下两步:

①. 查询 "销售部" 和 "市场部" 的部门ID
select id from dept where name = '销售部' or name = '市场部';

②. 根据部门ID, 查询员工信息
select * from emp 
where dept_id in 
(select id from dept where name = '销售部' or name = '市场部');

2. 查询比 财务部 所有人工资都高的员工信息（all）

# ①. 查询所有 财务部 人员工资
select id from dept where name = '财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');

# ②. 比 财务部 所有人工资都高的员工信息
select * from emp 
where salary > all 
( select salary from emp where dept_id =(select id from dept where name = '财务部') );

3. 查询比研发部其中任意一人(比最小值高)工资高的员工信息(any)
   分解为以下两步:

①. 查询研发部所有人工资
select salary from emp where dept_id = (select id from dept where name = '研发部');

②. 比研发部其中任意一人工资高的员工信息
select * from emp 
where salary > any 
( select salary from emp where dept_id =(select id from dept where name = '研发部') );

3. 返回location_id是1400或1700的部门中的所有员工姓名

   1. 查询location_id是1400或1700的部门编号
      SELECT distinct department_id
FROM departments
WHERE location_id IN(1400,1700)

   2. 查询员工姓名，要求部门号是①列表中的某一个
      SELECT last_name
FROM employees
WHERE department_id in
(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);

4. 返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary

   1. 查询job_id为‘IT_PROG’部门任一工资
      SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

   2. 查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
      SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < ANY
(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

      ) AND job_id <> 'IT_PROG';
      或(< any等价于< min)
      SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary <
(
SELECT min(salary)
FROM employees
WHERE job_id = 'IT_PROG'

      ) AND job_id<>'IT_PROG';

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：= 、<> 、IN 、NOT IN

A. 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;
这个需求同样可以拆解为两步进行:

①. 查询 "张无忌" 的薪资及直属领导
select salary, managerid from emp where name = '张无忌';

②. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
select * from emp 
where (salary,managerid) = (select salary, managerid from empwhere name = '张无忌');

查询员工编号最小并且工资最高的员工信息

1. 查询最小的员工编号
   SELECT MIN(employee_id)
FROM employees

2. 查询最高工资
   SELECT MAX(salary)
FROM employees

3. 查询员工信息
   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后面的子查询

• 仅仅支持标量子查询

1. 查询员工号=102的部门名
   SELECT
(
SELECT department_name,e.department_id
FROM departments d
INNER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102
) as 部门名;

from后面的子查询

• 将子查询结果充当一张表，要求必须起别名

1. 查询每个部门的平均工资的工资等级

   1. 查询每个部门的平均工资
      SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
      然后再
      SELECT * FROM job_grades;
      显示查询结果

   2. 连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT ag_dep.*,g.'grade_level'
FROM
(
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) as ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

exists后面（相关子查询）

语法：
exists(完整的查询语句)
结果：1或0

SELECT EXISTS(SELECT employee_id FROM employees);
结果为0。只关心他括号里的子查询有没有，有就为1

1. 查询有员工的部门名
   • 用in
     SELECT department_name
FROM departments d
WHERE d.'department_id' IN
(
SELECT department_id
FROM employees
);
   • 用exists
     SELECT department_name
FROM departments d
WHERE EXISTS
(
SELECT *
FROM employees e
WHERE d.'department_id'=e.'department_id'
);