SQL 自学笔记（8）子查询

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。

1 子查询的基本使用

• 例子引入：查询工资大于 Abel 的员工

-- 1. 之前所学：自连接
SELECT e2.last_name, e2.salary
FROM employees e1,
     employees e2
WHERE e1.last_name = 'Abel'
  AND e1.salary < e2.salary;

-- 2. 子查询
SELECT last_name, salary
FROM employees
WHERE salary > (SELECT salary
                FROM employees
                WHERE last_name = 'Abel');

1.1 基本格式

SELECT 列名
FROM 表名
WHERE 条件   
            (SELECT 列名
             FROM 表名
             WHERE 条件);

1.2 子查询分类

1.2.1 单行子查询 vs 多行子查询

• 查询的结果返回一条还是多条记录，将子查询分为单行子查询和多行子查询

1.2.2 相关子查询 vs 不相关子查询

• 不相关子查询：子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。

• 相关子查询：如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

2 单行子查询

2.1 常见案例

常见条件：=, >, >=, <=, <> ，要求子查询返回的是唯一一个结果

• 例如：上述的查询工资大于 Abel 的员工
• 例如：多条件

-- 多条件
SELECT last_name, salary, job_id
FROM employees
WHERE job_id = (SELECT job_id
                FROM employees
                WHERE employee_id = 103)
  AND salary < (SELECT salary
                FROM employees
                WHERE employee_id = 100);

【注意】当使用简单比较符时，单行子查询返回的结果必须唯一，如此才能比较。例如：若子查询查询到的 job_id 有多个，则无法比较。

• 例如：利用单行函数返回工资最少的员工信息

SELECT last_name, salary
FROM employees
WHERE salary = (SELECT MIN(salary)
                FROM employees);

2.2 HAVING 中的子查询

子查询优先执行，得到子查询的结果后返回给主句的 WHERE 或 HAVING 充当过滤条件的一部分。

• 例如：查询最低工资大于部门 department_id = 50 的最低工资的部门

SELECT department_id, MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) >
       (SELECT MIN(salary)
        FROM employees
        WHERE department_id = 50);

GROUP BY ... HAVING ... 用于分组查询，见聚合函数章节笔记。

2.3 CASE 中的子查询

• 例如：若员工的 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;

CASE WHEN 根据不同条件返回不同结果，见单行函数章节笔记。

2.4 子查询中的空值问题

如果子查询查不到结果，并不会报错，只是返回空。

• 例如：

SELECT last_name, salary
FROM employees
WHERE salary >
      (SELECT salary
       FROM employees
       WHERE last_name = 'Nobody');

2.5 常见错误：返回多行

使用单行子查询，返回的结果确实多行，则会报错

ERROR 1242 (21000): Subquery returns more than 1 row

单行比较符，需要比较的两者是唯一值，不可以一个值与多个值比较

3 多行子查询

3.1 案例引入

• 例如：查询与 141 号或 174 号员工的 manager_id 和 department_id 相同的其他员工的 employee_id manager_id 和 department_id

-- 1. 不成对比较，逐个增加条件
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN
      (SELECT manager_id
       FROM employees
       WHERE employee_id IN (174, 141))
  AND department_id IN
      (SELECT department_id
       FROM employees
       WHERE employee_id IN (174, 141))
  AND employee_id NOT IN (174, 141);

-- 2. 当作一个集合进行比较
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
      (SELECT manager_id, department_id
       FROM employees
       WHERE employee_id IN (141, 174))
  AND employee_id NOT IN (141, 174);

【补充】可以查看一下具体的条件

SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (141, 174);

3.2 常见多行比较操作符

操作符	含义
IN	等于列表中任意一个即可
ANY	和子查询返回的某个值比较
ALL	和子查询返回的所有值比较
SOME	和 ANY 相同

3.3 示例

3.3.1 ANY 和 ALL

• ANY ：返回其 job_id 中比 job_id 为 IT_PROG 部门任一工资低的员工信息

SELECT e.last_name, e.salary, e.job_id
FROM employees e
WHERE salary < ANY
      (SELECT e.salary
       FROM employees e
       WHERE e.job_id = 'IT_PROG')
  AND e.job_id != 'IT_PROG'
ORDER BY e.salary DESC;

子查询会返回很多行数据（不同的 salary）但我们需要找到的是只要比子查询的结果任意一个低即可，类似于 < MAX()

• ALL ：返回其 job_id 中比 job_id 为 IT_PROG 部门任意工资低的员工信息

SELECT e.last_name, e.salary, e.job_id
FROM employees e
WHERE salary < ALL
      (SELECT e.salary
       FROM employees e
       WHERE e.job_id = 'IT_PROG')
  AND e.job_id != 'IT_PROG'
ORDER BY e.salary DESC;

子查询会返回很多行数据（不同的 salary）但我们需要找到的是比子查询的结果任意都低，类似于 < MIN()

观察一下子查询的条件返回的结果

-- condition
SELECT e.salary
FROM employees e
WHERE e.job_id = 'IT_PROG';

最大为 9000 ，最小为 4200 ：在使用 ANY 时，小于 9000 即可；但在使用 ALL 时，却需要小于 4200 。这就是二者明显的不同。

3.3.2 FROM 后嵌套查询分组语句

• 查询平均工资最低的部门 id

-- 法 1: 直接使用 MIN
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT MIN(avg_sal)
                      FROM (SELECT AVG(salary) avg_sal
                            FROM employees
                            GROUP BY department_id) dept_avg_sal);

-- 法 2: 使用 ALL 间接表达最小
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (SELECT AVG(salary) avg_sal
                           FROM employees
                           GROUP BY department_id);

GROUP BY 和单行函数联合使用，返回多行结果

4 相关子查询

4.1 相关子查询的例子

例如：查询员工信息，筛选条件为其工资要大于他所在部门的平均工资。

SELECT e.last_name, e.salary, e.department_id
FROM employees e
WHERE salary >
      (SELECT AVG(salary)
       FROM employees
       WHERE department_id = e.department_id)
ORDER BY salary DESC;

如此，每次查一行数据时，就会执行子查询计算 department_id 等于当前行的平均工资，再返回给 WHERE 子句进行判断，决定是否查询这一行。

4.2 FROM 后使用子查询

同样是上面的例子

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 e1.salary > e2.dept_avg_sal
ORDER BY salary DESC;

FROM 后使用子查询，相当于创造了一张虚拟表，一定要使用 () 和别名。

4.3 ORDER BY 后使用子查询

SELECT employee_id, salary
FROM employees e
ORDER BY (SELECT d.department_name
          FROM departments d
          WHERE e.department_id = d.department_id);

4.4 EXISTS 与 NOT EXISTS

EXISTS ：检查在子查询中是否存在满足条件的行。逐行查询时：

• 如果在子查询中不存在满足条件的行：条件返回 FALSE 。继续在子查询中查找

• 如果在子查询中存在满足条件的行：条件返回 TRUE 。不在子查询中继续查找

NOT EXISTS 则相反。

例如：返回是公司管理者，即查找是否有 manager_id 等于这个员工的 employee.id

【方法一】EXISTS

SELECT employee_id, last_name
FROM employees e1
WHERE EXISTS (SELECT *
              FROM employees e2
              WHERE e2.manager_id =
                    e1.employee_id);

注意：EXISTS 返回的是 True 和 False

【方法二】自连接

或者使用自连接，即创造一个虚拟表

SELECT DISTINCT e1.employee_id, e1.last_name
FROM employees e1
         JOIN employees e2
WHERE e1.employee_id = e2.manager_id;

【推荐】：相比子查询，自连接更为高效

【方法三】多行子查询

SELECT employee_id, last_name
FROM employees
WHERE employee_id IN
      (SELECT DISTINCT manager_id
       FROM employees);

4.5 相关子查询去更新与更新

4.5.1 相关更新

使用相关子查询依据一个表中的数据更新另一个表的数据。

• 先增加一个字段

ALTER TABLE employees
    ADD(department_name VARCHAR(50));

mysql> DESC employees;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| employee_id     | int         | NO   | PRI | 0       |       |
| first_name      | varchar(20) | YES  |     | NULL    |       |
| last_name       | varchar(25) | NO   |     | NULL    |       |
| email           | varchar(25) | NO   | UNI | NULL    |       |
| phone_number    | varchar(20) | YES  |     | NULL    |       |
| hire_date       | date        | NO   |     | NULL    |       |
| job_id          | varchar(10) | NO   | MUL | NULL    |       |
| salary          | double(8,2) | YES  |     | NULL    |       |
| commission_pct  | double(2,2) | YES  |     | NULL    |       |
| manager_id      | int         | YES  | MUL | NULL    |       |
| department_id   | int         | YES  | MUL | NULL    |       |
| department_name | varchar(50) | YES  |     | NULL    |       | -- 新增字段
+-----------------+-------------+------+-----+---------+-------+
12 rows in set (0.00 sec)

• 然后更新

UPDATE employees e
SET department_name =
        (SELECT department_name
         FROM departments d
         WHERE e.department_id = d.department_id);

4.5.2 相关删除

使用相关子查询依据一个表中的数据删除另一个表的数据。

例如

DELETE FROM 表名1
WHERE 列名 IN
      (SELECT 列名
       FROM 表名2
       WHERE 条件);

IN 也可以是其他操作符