MySQL笔记 08：子查询

1. 定义

出现在其他语句中的 select 语句称为子查询或内查询，内部嵌套其他 select 语句的查询，称为外查询或主查询

2. 分类

按子查询出现的位置	按结果集的行列数不同
select 后：支持标量子查询	标量子查询：结果集为一行一列
from 后：支持表子查询	列子查询：结果集为一列多行
where 或 having 后：支持标量子查询，列子查询，行子查询	行子查询：结果集为一行多列
exists 后：支持表子查询	表子查询：结果集一般为多行多列

3. 实例

---

3.1 where 或 having 后的标量子查询（单行子查询）

案例：查询工资比Abel高的员工信息

SELECT 
  `employee_id`,
  `last_name`,
  `salary` 
FROM
  `employees` 
WHERE `salary` > 
  (SELECT 
    `salary` 
  FROM
    `employees` 
  WHERE `last_name` = 'Abel')

案例：查询job_id与141号员工相同，且工资比143号员工多的员工的姓名，job_id和工资

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)

案例：返回公司工资最少的员工的姓名，job_id和工资

SELECT 
  `last_name`,
  `job_id`,
  `salary` 
FROM
  `employees` 
WHERE `salary` = 
  (SELECT 
    MIN(`salary`) 
  FROM
    `employees`)

案例：查询最低工资大于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)

3.2 where 或 having 后的列子查询（多行子查询）

案例：查询location_id是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任一工资低的员工信息

SELECT 
  `employee_id`,
  `last_name`,
  `job_id`,
  `salary` 
FROM
  `employees` 
WHERE `salary` < SOME 
  (SELECT DISTINCT 
    `salary` 
  FROM
    `employees` 
  WHERE `job_id` = "IT_PROG") 
  AND `job_id` != "IT_PROG" 

案例：查询其他部门中比job_id为IT_PROG所有工资都低的员工信息

SELECT 
  `employee_id`,
  `last_name`,
  `job_id`,
  `salary` 
FROM
  `employees` 
WHERE `salary` < ALL 
  (SELECT DISTINCT 
    `salary` 
  FROM
    `employees` 
  WHERE `job_id` = "IT_PROG") 
  AND `job_id` != "IT_PROG" 

3.3 where 或 having 后的行子查询

案例：查询员工编号最小且工资最高的员工信息

SELECT 
  * 
FROM
  `employees` 
WHERE (`employee_id`, `salary`) = 
  (SELECT 
    MIN(`employee_id`),
    MAX(`salary`) 
  FROM
    `employees`)

3.4 select 后的子查询

案例：查询每个部门的员工个数

SELECT 
  d.`department_id`,
  d.`department_name`,
  (SELECT 
    COUNT(*) 
  FROM
    `employees` e 
  WHERE e.`department_id` = d.`department_id`) AS "部门人数" 
FROM
  `departments` d 

案例：查询员工号为102的部门名

SELECT 
  (SELECT 
    `department_name` 
  FROM
    `departments` d 
    INNER JOIN `employees` e 
      ON e.`department_id` = d.`department_id` 
  WHERE e.`employee_id` = 102)

3.5 from 后的子查询

案例：查询每个部门的平均工资的工资等级

SELECT 
  a.`avg`,
  `grade_level` 
FROM
  (
    (SELECT 
      `department_id`,
      AVG(`salary`) AS `avg` 
    FROM
      `employees` 
    GROUP BY `department_id`) AS a 
    INNER JOIN `job_grades` 
      ON a.`avg` BETWEEN `lowest_sal` 
      AND `highest_sal`
  )

3.6 exists后的子查询（相关子查询）

案例：查询是否存在工资大于30000的员工

-- 0 只关注子查询的结果集是否为空
SELECT 
  EXISTS 
  (SELECT 
    `employee_id` 
  FROM
    `employees` 
  WHERE `salary` > 30000)

案例：查询有员工的部门名

SELECT 
  `department_name` 
FROM
  `departments` AS d 
WHERE EXISTS 
  (SELECT 
    `employee_id` 
  FROM
    `employees` AS e 
  WHERE d.`department_id` = e.`department_id`)

SELECT 
  `department_name` 
FROM
  `departments` 
WHERE `department_id` IN 
  (SELECT DISTINCT 
    `department_id` 
  FROM
    `employees` );

案例：查询没有女朋友的男神信息

SELECT 
  * 
FROM
  `boys` AS bo 
WHERE ! EXISTS 
  (SELECT 
    * 
  FROM
    `beauty` AS b 
  WHERE b.`boyfriend_id` = bo.`id`)

---

总结

1. 子查询放在小括号内
2. 子查询一般放在条件的右侧
3. 标量子查询搭配单行操作符（条件运算符）使用，列子查询一般搭配多行操作符（in，some， all）使用
4. 子查询的执行是优先于主查询的

---