MySQL-09.子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL4.1开始引入。
SQL中子查询的使用大大增强了SELECT查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算出一个数据结果，然后与这个数据结果(可能是单行，也可能是多行)进行比较。
1.需求分析与问题解决

1.1 实际问题

查询出employees表中，比Abel工资高的员工信息。

1. -- 方式1多表查询 自连接
2. SELECT
3.     e2.`last_name`,
4.     e2.`salary`
5. FROM
6.     employees e1,
7.     employees e2
8. WHERE e2.`salary` > e1.`salary`
9.     AND e1.`last_name` = 'Abel';-- 相当于自连接e1表(一张只有一行数据的employees表)
11. -- 方式2 子查询
12. SELECT
13.     last_name,
14.     salary
15. FROM
16.     employees
17. WHERE salary >
18.     (SELECT
19.         salary
20.     FROM
21.         employees
22.     WHERE last_name = 'Abel');

复制代码

1.2 子查询的基本使用

• 子查询的基本语法结构。
  

1. SELECT select_list
2. FROM table_name
3. WHERE expr operator
4.                                 (SELECT select_list
5.                  FROM table_name
6.                 );

复制代码

• 子查询(内查询)在主查询之前一次执行完成。
  
• 子查询的结果被主查询(外查询)使用。
  
• 注意事项
  
  
  
  • 子查询要包含在括号内。
    
  • 将子查询放在比较条件的右侧。
    
  • 单行操作符对于单行子查询，多行操作符对于多行子查询。
    
  
  

1.3 子查询的分类

分类方式1:

按照内查询的结果返回一条还是多条记录，将子查询分为单行子查询，多行子查询。
分类方式2:

我们按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。
子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。
如果，子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的方式，就称为相关子查询。
2.单行子查询

2.1 单行比较操作符


2.2 代码示例

题目：查询工资大于149号员工工资的员工的信息

1. mysql> SELECT last_name,employee_id,salary
2.     -> FROM employees
3.     -> WHERE salary > (
4.     ->                 SELECT salary FROM employees WHERE employee_id = 149
5.     ->                 );
6. +-----------+-------------+----------+
7. | last_name | employee_id | salary   |
8. +-----------+-------------+----------+
9. | King      |         100 | 24000.00 |
10. | Kochhar   |         101 | 17000.00 |
11. | De Haan   |         102 | 17000.00 |
12. ...
13. | Hartstein |         201 | 13000.00 |
14. | Higgins   |         205 | 12000.00 |
15. +-----------+-------------+----------+
16. 13 rows in set (0.00 sec)

复制代码

题目: 返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

1. mysql> SELECT last_name,job_id,salary
2.     -> FROM employees
3.     -> WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 149)
4.     -> AND salary > (SELECT salary FROM employees WHERE employee_id = 143);
5. +-----------+--------+----------+
6. | last_name | job_id | salary   |
7. +-----------+--------+----------+
8. | Russell   | SA_MAN | 14000.00 |
9. | Partners  | SA_MAN | 13500.00 |
10. | Errazuriz | SA_MAN | 12000.00 |
11. | Cambrault | SA_MAN | 11000.00 |
12. | Zlotkey   | SA_MAN | 10500.00 |
13. +-----------+--------+----------+
14. 5 rows in set (0.00 sec)

复制代码

题目 :返回公司工资最少的员工的last_name,job_id和salary

1. mysql> SELECT last_name,job_id,salary
2.     -> FROM employees
3.     -> WHERE salary = (SELECT MIN(salary) FROM employees);
4. +-----------+----------+---------+
5. | last_name | job_id   | salary  |
6. +-----------+----------+---------+
7. | Olson     | ST_CLERK | 2100.00 |
8. +-----------+----------+---------+
9. 1 row in set (0.00 sec)

复制代码

扩展,如何求出工资倒数第二的工资的员工的last_name,job_id和salary

1. mysql> SELECT last_name,job_id,salary
2.     -> FROM employees
3.     -> WHERE salary = (SELECT MIN(salary) FROM employees WHERE salary != (SELECT MIN(salary) FROM employees));
4. +------------+----------+---------+
5. | last_name  | job_id   | salary  |
6. +------------+----------+---------+
7. | Markle     | ST_CLERK | 2200.00 |
8. | Philtanker | ST_CLERK | 2200.00 |
9. +------------+----------+---------+
10. 2 rows in set (0.00 sec)

复制代码

来源，这是我再leetcode刷到过的sql的中等题目
思路解析，WHERE salary != (SELECT MIN(salary) FROM employees) 就是，过滤掉倒数第一的工资，排除倒数第一的工资的倒数第一的工资就是倒数第二，当时觉得这个思路，特别好，记忆比较深刻。
题目，查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id
题目 其实是多行子查询(返回结果是多行的)
不成对比较

1. mysql> SELECT employee_id,manager_id,department_id
2.     -> FROM employees
3.     -> WHERE manager_id IN (SELECT manager_id FROM employees WHERE employee_id IN (141,174))
4.     -> AND department_id IN (SELECT department_id FROM employees WHERE employee_id IN (141,174))
5.     -> AND employee_id NOT IN(141,174);
6. +-------------+------------+---------------+
7. | employee_id | manager_id | department_id |
8. +-------------+------------+---------------+
9. |         142 |        124 |            50 |
10. |         143 |        124 |            50 |
11. |         144 |        124 |            50 |
12. |         196 |        124 |            50 |
13. |         197 |        124 |            50 |
14. |         198 |        124 |            50 |
15. |         199 |        124 |            50 |
16. |         175 |        149 |            80 |
17. |         176 |        149 |            80 |
18. |         177 |        149 |            80 |
19. |         179 |        149 |            80 |
20. +-------------+------------+---------------+
21. 11 rows in set (0.00 sec)

复制代码

成对比较(使用的条件很苛刻，用的很少)

1. mysql> SELECT employee_id,manager_id,department_id
2.     -> FROM employees
3.     -> WHERE (manager_id,department_id) IN (SELECT manager_id,department_id FROM employees WHERE employee_id IN (141,174))
4.     -> AND employee_id NOT IN(141,174);
5. +-------------+------------+---------------+
6. | employee_id | manager_id | department_id |
7. +-------------+------------+---------------+
8. |         142 |        124 |            50 |
9. |         143 |        124 |            50 |
10. |         144 |        124 |            50 |
11. |         196 |        124 |            50 |
12. |         197 |        124 |            50 |
13. |         198 |        124 |            50 |
14. |         199 |        124 |            50 |
15. |         175 |        149 |            80 |
16. |         176 |        149 |            80 |
17. |         177 |        149 |            80 |
18. |         179 |        149 |            80 |
19. +-------------+------------+---------------+
20. 11 rows in set (0.00 sec)

复制代码

2.3 HAVING中的子查询

• 首先执行子查询
  
• 向主查询中的HAVING子句返回结果
  

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

1. mysql> SELECT department_id,MIN(salary)
2.     -> FROM employees
3.     -> GROUP BY department_id
4.     -> HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);
5. +---------------+-------------+
6. | department_id | MIN(salary) |
7. +---------------+-------------+
8. |          NULL |     7000.00 |
9. |            10 |     4400.00 |
10. |            20 |     6000.00 |
11. |            30 |     2500.00 |
12. |            40 |     6500.00 |
13. |            60 |     4200.00 |
14. |            70 |    10000.00 |
15. |            80 |     6100.00 |
16. |            90 |    17000.00 |
17. |           100 |     6900.00 |
18. |           110 |     8300.00 |
19. +---------------+-------------+
20. 11 rows in set (0.00 sec)

复制代码

2.4 CASE中的子查询

题目：显示员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。

1. SELECT
2.     employee_id,
3.     last_name,
4.     (
5.         CASE
6.             department_id
7.             WHEN
8.             (SELECT
9.                 department_id
10.             FROM
11.                 departments
12.             WHERE location_id = 1800)
13.             THEN 'Canadad'
14.             ELSE 'USA'
15.         END
16.     ) AS 'location'
17. FROM
18.     employees

复制代码

2.5 子查询中的空值现象

出现原因是，子查询中为查到数据，也可能是子查询中查到的数据中包含null(多行子查询中出现空值现象的原因)

1. mysql> SELECT last_name,salary,job_id
2.     -> FROM employees
3.     -> WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'wind');
4. Empty set (0.00 sec)

复制代码

2.6 非法使用子查询

1. mysql> SELECT last_name,salary,job_id
2.     -> FROM employees
3.     -> WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id);
4. ERROR 1242 (21000): Subquery returns more than 1 row

复制代码

多行子查询使用单行比较符
3.多行子查询

• 也称为集合比较子查询
  
• 子查询返回多行
  
• 使用多行比较操作符
  

3.1 多行比较操作符


3.2 代码示例

题目 返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

1. mysql> SELECT employee_id,last_name,job_id,salary
2.     -> FROM employees
3.     -> WHERE salary < ANY (SELECT salary FROM employees
4.     -> WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
5. +-------------+-------------+------------+---------+
6. | employee_id | last_name   | job_id     | salary  |
7. +-------------+-------------+------------+---------+
8. ...
9. |         202 | Fay         | MK_REP     | 6000.00 |
10. |         203 | Mavris      | HR_REP     | 6500.00 |
11. |         206 | Gietz       | AC_ACCOUNT | 8300.00 |
12. +-------------+-------------+------------+---------+
13. 76 rows in set (0.00 sec)

复制代码

1. -- 上述sql语句也可以转化为下面这条sql语句，是因为比任一工资低的条件，可以转化为比最大值低即可
2. -- 这是因为比除最大值外的值低的条件，会隐形的包含在比最大值低的情况下
3. SELECT employee_id,last_name,job_id,salary
4. FROM employees
5. WHERE salary < (SELECT MAX(salary) FROM employees
6. WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';

复制代码

题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary

1. mysql> SELECT employee_id,last_name,job_id,salary
2.     -> FROM employees
3.     -> WHERE salary < ALL (SELECT salary FROM employees
4.     -> WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
5. +-------------+-------------+----------+---------+
6. | employee_id | last_name   | job_id   | salary  |
7. +-------------+-------------+----------+---------+
8. |         115 | Khoo        | PU_CLERK | 3100.00 |
9. |         116 | Baida       | PU_CLERK | 2900.00 |
10. |         117 | Tobias      | PU_CLERK | 2800.00 |
11. ...
12. |         197 | Feeney      | SH_CLERK | 3000.00 |
13. |         198 | OConnell    | SH_CLERK | 2600.00 |
14. |         199 | Grant       | SH_CLERK | 2600.00 |
15. +-------------+-------------+----------+---------+
16. 44 rows in set (0.00 sec)

复制代码

1. -- 上述sql语句也可以转化成下面这条SQL语句，是因为比所有的工资都低的员工，可以转成比最小值低即可
2. -- 这是因为比除最小值的值低的条件，会隐形的包含在比最小值低的情况下
3. SELECT employee_id,last_name,job_id,salary
4. FROM employees
5. WHERE salary < (SELECT MIN(salary) FROM employees
6. WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';

复制代码

题目：查询平均工资最低的部门id
思路，按照部门id分类，查出各部门的平均工资，然后找出小于等于所有部门平均工资的平均工资对于的部门id。

1. mysql> SELECT
2.     ->     department_id
3.     -> FROM
4.     ->     employees
5.     -> GROUP BY department_id
6.     -> HAVING AVG(salary) <= ALL
7.     ->     (SELECT
8.     ->         AVG(salary)
9.     ->     FROM
10.     ->         employees
11.     ->     GROUP BY department_id);
12. +---------------+
13. | department_id |
14. +---------------+
15. |            50 |
16. +---------------+
17. 1 row in set (0.00 sec)

复制代码

1. -- 上述sql可以转化为下面SQL语句
2. -- 思路，找出最小的平均工资对应的部门
3. SELECT department_id
4. FROM employees
5. GROUP BY department_id
6. HAVING AVG(salary) = (
7.             SELECT MIN(avg_salary)
8.             FROM (
9.                 SELECT AVG(salary) AS avg_salary
10.                 FROM employees
11.                 GROUP BY department_id
12.             ) t
13.         );

复制代码

注意，在MYSQL中聚合函数不能嵌套使用。

1. mysql> SELECT
2.     ->         MIN(AVG(salary))
3.     ->     FROM
4.     ->         employees
5.     ->     GROUP BY department_id;
6. ERROR 1111 (HY000): Invalid use of group function

复制代码

3.3 空值问题

1. mysql> SELECT employee_id
2.     -> FROM employees
3.     -> WHERE employee_id NOT IN(
4.     ->         SELECT manager_id
5.     ->         FROM employees
6.     ->         );
7. Empty set (0.00 sec)

复制代码

1. -- sql分析，原本是想获取不在管理者中的员工信息，但是因为子查询中存在null值，导致整个查询为空
2. SELECT employee_id,last_name
3. FROM employees
4. WHERE employee_id NOT IN(
5.             SELECT manager_id
6.             FROM employees
7.             );
8. -- 获取不在管理者中的员工信息 正确的写法            
9. SELECT employee_id,last_name
10. FROM employees
11. WHERE employee_id NOT IN(
12.             SELECT manager_id
13.             FROM employees
14.                     WHERE manager_id IS NOT NULL
15.             );

复制代码

4.相关子查询

4.1 相关子查询执行流程

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就被称为关联子查询。
相关子查询按照一行一行的顺序执行，主查询的每一行都执行一次子查询。

1. -- 相关子查询语法 子查询中使用主查询中的列
2. SELECT column1,column2,...
3. FROM table1 outer
4. WHERE column1 operator
5.                                         (SELECT column1,column2
6.                                FROM table2
7.                        WHERE expr1 = outer.expr2
8.                        );

复制代码

4.2 代码示例

题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
方式一  相关子查询

1. mysql> SELECT last_name,salary,department_id
2.     -> FROM employees e
3.     -> WHERE salary > (
4.     ->             SELECT AVG(salary)
5.     ->             FROM employees
6.     ->             WHERE department_id = e.`department_id`
7.     ->             GROUP BY department_id
8.     ->             );
9. +-----------+----------+---------------+
10. | last_name | salary   | department_id |
11. +-----------+----------+---------------+
12. | King      | 24000.00 |            90 |
13. | Hunold    |  9000.00 |            60 |
14. | Ernst     |  6000.00 |            60 |
15. | Greenberg | 12000.00 |           100 |
16. ...
17. | Everett   |  3900.00 |            50 |
18. | Hartstein | 13000.00 |            20 |
19. | Higgins   | 12000.00 |           110 |
20. +-----------+----------+---------------+
21. 38 rows in set (0.00 sec)

复制代码

方式二 FROM子句中使用子查询

1. mysql> SELECT last_name,salary,e1.department_id
2.     -> FROM employees e1,(SELECT AVG(salary) AS avg_sal,department_id FROM employees GROUP BY department_id) e2
3.     -> WHERE e1.`salary` > e2.avg_sal AND e1.`department_id` = e2.department_id;
4. +-----------+----------+---------------+
5. | last_name | salary   | department_id |
6. +-----------+----------+---------------+
7. | Hartstein | 13000.00 |            20 |
8. | Raphaely  | 11000.00 |            30 |
9. ...
10. | Greenberg | 12000.00 |           100 |
11. | Faviet    |  9000.00 |           100 |
12. | Higgins   | 12000.00 |           110 |
13. +-----------+----------+---------------+
14. 38 rows in set (0.00 sec)

复制代码

FROM子句中使用子查询，子查询是作为FROM的一部分，子查询要用()引起来，并且要给这个子查询起别名，把它当作一张临时表使用。
在ORDER BY中使用子查询
题目：查询员工的id,salary,按照department_name 排序

1. SELECT employee_id,salary
2. FROM employees e
3. ORDER BY (
4.     SELECT department_name
5.     FROM departments
6.     WHERE department_id = e.`department_id`
7.     );

复制代码

题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id

1. SELECT employee_id,last_name,job_id
2. FROM employees e
3. WHERE 2 <= (
4.     SELECT COUNT(*)
5.     FROM job_history
6.     WHERE employee_id = e.`employee_id`
7.     );

复制代码

问题

自连接和子查询那种好？
答案

自连接更好！如果需求可以使用自连接，也可以使用子查询的情况下，建议使用自连接。原因，因为在许多DBMS的处理过程中，对于自连接的处理速度要比子查询快的多。
可以这样理解，子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此大部分的DBMS中都对自连接进行了优化。
只是为了记录自己的学习历程，且本人水平有限，不对之处，请指正。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！