第12篇：子查询（本篇非常重要，高手必备）-Java充电社【公众号：Java充电社】

打算提升sql技能的，可以加我微信itsoku，带你成为sql高手。

这是Mysql系列第12篇。

环境：mysql5.7.25，cmd命令中进行演示。

**本章节非常重要。**

## 子查询

出现在select语句中的select语句，称为子查询或内查询。

外部的select查询语句，称为主查询或外查询。

## 子查询分类

### 按结果集的行列数不同分为4种

- 标量子查询（结果集只有一行一列）
- 列子查询（结果集只有一列多行）
- 行子查询（结果集有一行多列）
- 表子查询（结果集一般为多行多列）

### 按子查询出现在主查询中的不同位置分

- **select后面**：仅仅支持标量子查询。

- **from后面**：支持表子查询。

- **where或having后面**：支持标量子查询（单列单行）、列子查询（单列多行）、行子查询（多列多行）

- **exists后面（即相关子查询）**：表子查询（多行、多列）

## 准备测试数据

测试数据比较多，放在我的个人博客上了。

浏览器中打开链接：<a href="http://www.itsoku.com/article/196" target="_blank">http://www.itsoku.com/article/196</a>

mysql中执行里面的`javacode2018_employees库`部分的脚本。

成功创建`javacode2018_employees`库及5张表，如下：

| 表名        | 描述                     |
| ----------- | ------------------------ |
| departments | 部门表                   |
| employees   | 员工信息表               |
| jobs        | 职位信息表               |
| locations   | 位置表（部门表中会用到） |
| job_grades  | 薪资等级表               |

## select后面的子查询

> 子查询位于select后面的，**仅仅支持标量子查询**。

### 示例1

> 查询每个部门员工个数

```java
SELECT
  a.*,
  (SELECT count(*)
   FROM employees b
   WHERE b.department_id = a.department_id) AS 员工个数
FROM departments a;
```

### 示例2

> 查询员工号=102的部门名称

```java
SELECT (SELECT a.department_name
        FROM departments a, employees b
        WHERE a.department_id = b.department_id
              AND b.employee_id = 102) AS 部门名;
```

## from后面的子查询

> 将子查询的结果集充当一张表，要求必须起别名，否者这个表找不到。
>
> 然后将真实的表和子查询结果表进行连接查询。

### 示例1

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

```java
-- 查询每个部门平均工资
SELECT
  department_id,
  avg(a.salary)
FROM employees a
GROUP BY a.department_id;

-- 薪资等级表
SELECT *
FROM job_grades;

-- 将上面2个结果连接查询，筛选条件:平均工资 between lowest_sal and highest_sal;
SELECT
  t1.department_id,
  sa AS '平均工资',
  t2.grade_level
FROM (SELECT
        department_id,
        avg(a.salary) sa
      FROM employees a
      GROUP BY a.department_id) t1, job_grades t2
WHERE
  t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
```

运行最后一条结果如下：

```java
mysql> SELECT
          t1.department_id,
          sa AS '平均工资',
          t2.grade_level
        FROM (SELECT
                department_id,
                avg(a.salary) sa
              FROM employees a
              GROUP BY a.department_id) t1, job_grades t2
        WHERE
          t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
+---------------+--------------+-------------+
| department_id | 平均工资     | grade_level |
+---------------+--------------+-------------+
|          NULL |  7000.000000 | C           |
|            10 |  4400.000000 | B           |
|            20 |  9500.000000 | C           |
|            30 |  4150.000000 | B           |
|            40 |  6500.000000 | C           |
|            50 |  3475.555556 | B           |
|            60 |  5760.000000 | B           |
|            70 | 10000.000000 | D           |
|            80 |  8955.882353 | C           |
|            90 | 19333.333333 | E           |
|           100 |  8600.000000 | C           |
|           110 | 10150.000000 | D           |
+---------------+--------------+-------------+
12 rows in set (0.00 sec)
```

## where和having后面的子查询

### where或having后面，可以使用

1. 标量子查询（单行单列行子查询）
2. 列子查询（单列多行子查询）
3. 行子查询（一行多列）

### 特点

1. 子查询放在小括号内。
2. 子查询一般放在条件的右侧。
3. 标量子查询，一般搭配着单行单列操作符使用   **>、<、>=、<=、=、<>、!=**
4. 列子查询，一般搭配着多行操作符使用

> in(not in)：列表中的“任意一个”
   >
   > any或者some：和子查询返回的“某一个值”比较，比如a>some(10,20,30)，a大于子查询中任意一个即可，a大于子查询中最小值即可，等同于a>min(10,20,30)。
   >
   > all：和子查询返回的“所有值”比较，比如a>all(10,20,30)，a大于子查询中所有值，换句话说，a大于子查询中最大值即可满足查询条件，等同于a>max(10,20,30);

5. 子查询的执行优先于主查询执行，因为主查询的条件用到了子查询的结果。

### mysql中的in、any、some、all

in，any，some，all分别是子查询关键词之一。

**in**：in常用于where表达式中，其作用是查询某个范围内的数据

**any和some一样**： 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。

**all**：可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

下文中会经常用到这些关键字。

### 标量子查询

#### 一般标量子查询，示例

> 查询谁的工资比Abel的高？

```java
/*①查询abel的工资【改查询是标量子查询】*/
SELECT salary
FROM employees
WHERE last_name = 'Abel';

/*②查询员工信息，满足salary>①的结果*/
SELECT *
FROM employees a
WHERE a.salary > (SELECT salary
                  FROM employees
                  WHERE last_name = 'Abel');
```

#### 多个标量子查询，示例

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

```java
/*返回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;
/*③查询员工的姓名、job_id、工资，要求job_id=① and salary>②*/
SELECT
  a.last_name 姓名,
  a.job_id,
  a.salary    工资
FROM employees a
WHERE a.job_id = (SELECT job_id
                  FROM employees
                  WHERE employee_id = 141)
      AND
      a.salary > (SELECT salary
                  FROM employees
                  WHERE employee_id = 143);
```

#### 子查询+分组函数，示例

> 查询最低工资大于50号部门最低工资的部门id和其最低工资【having】

```java
/*查询最低工资大于50号部门最低工资的部门id和其最低工资【having】*/
/*①查询50号部门的最低工资*/
SELECT min(salary)
FROM employees
WHERE department_id = 50;
/*②查询每个部门的最低工资*/
SELECT
  min(salary),
  department_id
FROM employees
GROUP BY department_id;
/*③在②的基础上筛选，满足min(salary)>①*/
SELECT
  min(a.salary) minsalary,
  department_id
FROM employees a
GROUP BY a.department_id
HAVING min(a.salary) > (SELECT min(salary)
                        FROM employees
                        WHERE department_id = 50);
```

#### 错误的标量子查询，示例

> 将上面的示例③中子查询语句中的min(salary)改为salary，执行效果如下：

```java
mysql> SELECT
          min(a.salary) minsalary,
          department_id
        FROM employees a
        GROUP BY a.department_id
        HAVING min(a.salary) > (SELECT salary
                                FROM employees
                                WHERE department_id = 500000);
ERROR 1242 (21000): Subquery returns more than 1 row
```

> 错误提示：子查询返回的结果超过了1行记录。
>
> 说明：上面的子查询只支持**最多一列一行记录**。

### 列子查询(子查询结果集一列多行)

> 列子查询需要搭配多行操作符使用：in(not in)、any/some、all。
>
> 为了提升效率，最好去重一下**distinct**关键字。

#### 示例1

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

```java
/*返回location_id是1400或1700的部门中的所有员工姓名*/
/*方式1*/
/*①查询location_id是1400或1700的部门编号*/
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400, 1700);

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

/*方式2：使用any实现*/
SELECT a.last_name
FROM employees a
WHERE a.department_id = ANY (SELECT DISTINCT department_id
                             FROM departments
                             WHERE location_id IN (1400, 1700));

/*拓展，下面与not in等价*/
SELECT a.last_name
FROM employees a
WHERE a.department_id <> ALL (SELECT DISTINCT department_id
                             FROM departments
                             WHERE location_id IN (1400, 1700));
```

#### 示例2

> 返回其他工种中比job_id为'IT_PROG'工种任意工资低的员工的员工号、姓名、job_id、salary

```java
/*返回其他工种中比job_id为'IT_PROG'工种任一工资低的员工的员工号、姓名、job_id、salary*/
/*①查询job_id为'IT_PROG'部门任-工资*/
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';

/*②查询员工号、姓名、job_id、salary，slary<①的任意一个*/
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';

/*或者*/
SELECT
  last_name,
  employee_id,
  job_id,
  salary
FROM employees
WHERE salary < (SELECT max(salary)
                FROM employees
                WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
```

#### 示例3

> 返回其他工种中比job_id为'IT_PROG'部门所有工资低的员工的员工号、姓名、job_id、salary

```java
/*返回其他工种中比job_id为'IT_PROG'部门所有工资低的员工的员工号、姓名、job_id、salary*/
SELECT
  last_name,
  employee_id,
  job_id,
  salary
FROM employees
WHERE salary < ALL (SELECT DISTINCT salary
                    FROM employees
                    WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';

/*或者*/
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';
```

### 行子查询（子查询结果集一行多列）

#### 示例

> 查询员工编号最小并且工资最高的员工信息，3种方式。

```java
/*查询员工编号最小并且工资最高的员工信息*/
/*①查询最小的员工编号*/
SELECT min(employee_id)
FROM employees;
/*②查询最高工资*/
SELECT max(salary)
FROM employees;
/*③方式1：查询员工信息*/
SELECT *
FROM employees a
WHERE a.employee_id = (SELECT min(employee_id)
                       FROM employees)
      AND salary = (SELECT max(salary)
                    FROM employees);

/*方式2*/
SELECT *
FROM employees a
WHERE (a.employee_id, a.salary) = (SELECT
                                     min(employee_id),
                                     max(salary)
                                   FROM employees);
/*方式3*/
SELECT *
FROM employees a
WHERE (a.employee_id, a.salary) in (SELECT
                                     min(employee_id),
                                     max(salary)
                                   FROM employees);
```

> 方式1比较常见，方式2、3更简洁。

### exists后面（也叫做相关子查询）

1. 语法：exists(完整的查询语句)。
2. exists查询结果：1或0，exists查询的结果用来判断子查询的结果集中是否有值。
3. 一般来说，能用exists的子查询，绝对都能用in代替，所以exists用的少。
4. 和前面的查询不同，这先执行主查询，然后主查询查询的结果，在根据子查询进行过滤，子查询中涉及到主查询中用到的字段，所以叫相关子查询。

#### 示例1

> 简单示例

```java
mysql> SELECT exists(SELECT employee_id
              FROM employees
              WHERE salary = 300000) AS 'exists返回1或者0';
+----------------------+
| exists返回1或者0     |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)
```

#### 示例2

> 查询所有员工的部门名称

```java
/*exists入门案例*/
SELECT exists(SELECT employee_id
              FROM employees
              WHERE salary = 300000) AS 'exists返回1或者0';

/*查询所有员工部门名*/
SELECT department_name
FROM departments a
WHERE exists(SELECT 1
             FROM employees b
             WHERE a.department_id = b.department_id);

/*使用in实现*/
SELECT department_name
FROM departments a
WHERE a.department_id IN (SELECT department_id
                          FROM employees);
```

#### 示例3

> 查询没有员工的部门

```java
/*查询没有员工的部门*/
/*exists实现*/
SELECT *
FROM departments a
WHERE NOT exists(SELECT 1
                 FROM employees b
                 WHERE a.department_id = b.department_id AND b.department_id IS NOT NULL);
/*in的方式*/
SELECT *
FROM departments a
WHERE a.department_id NOT IN (SELECT department_id
                              FROM employees b
                              WHERE b.department_id IS NOT NULL);
```

> 上面脚本中有`b.department_id IS NOT NULL`，为什么，有大坑，向下看。

## NULL的大坑

### 示例1

> 使用**not in**的方式查询没有员工的部门，如下：

```java
SELECT *
FROM departments a
WHERE a.department_id NOT IN (SELECT department_id
                              FROM employees b);
```

运行结果：

```java
mysql> SELECT *
    -> FROM departments a
    -> WHERE a.department_id NOT IN (SELECT department_id
    ->                               FROM employees b);
Empty set (0.00 sec)
```

not in的情况下，子查询中列的值为NULL的时候，外查询的结果为空。

**建议：建表是，列不允许为空。**

## 总结

1. 本文中讲解了常见的子查询，请大家务必多练习
2. 注意in、any、some、any的用法
3. **字段值为NULL的时候，not in查询有大坑，这个要注意**
4. 建议创建表的时候，列不允许为空

## Mysql系列目录

1. **<a target="_blank" href="https://mp.weixin.qq.com/s?__biz=MzA5MTkxMDQ4MQ==&mid=2648933257&idx=1&sn=0f0086a2465a2fcae13d3fea65064803&chksm=88621bb7bf1592a1ac94fe4107ba1ef26a0fa97e1bf9aea7279009d8bd240f1ef7d27aa10393&token=1876080189&lang=zh_CN#rd">第1篇：mysql基础知识</a>**
2. **<a target="_blank" href="https://mp.weixin.qq.com/s?__biz=MzA5MTkxMDQ4MQ==&mid=2648933270&idx=1&sn=409080e17352da2035b0bfdf63ccdfde&chksm=88621ba8bf1592beb2ef6106d6bf9f3eccd48d6814c7031f36e3c8be68821f17cf065129688c&token=1876080189&lang=zh_CN#rd"> 第2篇：详解mysql数据类型（重点）</a>**
3. **<a target="_blank" href="https://mp.weixin.qq.com/s?__biz=MzA5MTkxMDQ4MQ==&mid=2648933279&idx=1&sn=f8591b95362cb3c352d895b1289d665a&chksm=88621ba1bf1592b72a43a62e3f310695e8b87f17932d052145622c3edbb70ef8cb987849fc3e&token=516655478&lang=zh_CN#rd"> 第3篇：管理员必备技能(必须掌握)</a>**
4. **<a target="_blank" href="https://mp.weixin.qq.com/s?__biz=MzA5MTkxMDQ4MQ==&mid=2648933289&idx=1&sn=c4f212c312ea86e08ad322caddd05e38&chksm=88621b97bf159281156ee3be510a1a15234531d2c97d66957e67377829ab23779809ea55bbde&token=1484565200&lang=zh_CN#rd"> 第4篇：DDL常见操作</a>**
5. **<a target="_blank" href="https://mp.weixin.qq.com/s?__biz=MzA5MTkxMDQ4MQ==&mid=2648933296&idx=1&sn=1c56256d60c5847a944d87c8cfc9c14d&chksm=88621b8ebf159298b0789e2994d2aaf8b582effc7d8c1ba715deaca11c86a9dc8ac730878dc0&token=2000571846&lang=zh_CN#rd"> 第5篇：DML操作汇总（insert,update,delete）</a>**
6. **<a target="_blank" href="https://mp.weixin.qq.com/s?__biz=MzA5MTkxMDQ4MQ==&mid=2648933300&idx=1&sn=bedef4d430dc76141e42e42ef6acfaa6&chksm=88621b8abf15929caae7904019c946a396885a33855ca465bacdd4187538005ebc3c116888f5&token=1814800041&lang=zh_CN#rd"> 第6篇：select查询基础篇</a>**
7. **<a target="_blank" href="https://mp.weixin.qq.com/s?__biz=MzA5MTkxMDQ4MQ==&mid=2648933305&idx=1&sn=2c5ba0bea6fcdd57d86cecd63541f91a&chksm=88621b87bf1592915086c1e945119fcc95af6aa1127b90ef1b56b018083bfd787ad95efde918&token=1144227002&lang=zh_CN#rd"> 第7篇：玩转select条件查询，避免采坑</a>**
8. **<a target="_blank" href="https://mp.weixin.qq.com/s?__biz=MzA5MTkxMDQ4MQ==&mid=2648933309&idx=1&sn=0f7dfec8bc70e67daa7159ee219325b8&chksm=88621b83bf1592951d949179061f39a1f4266b2879bc9a25af4da3b4b29f69ab1fcc595a462f&token=516674265&lang=zh_CN#rd"> 第8篇：详解排序和分页(order by & limit)</a>**
9. **<a target="_blank" href="https://mp.weixin.qq.com/s?__biz=MzA5MTkxMDQ4MQ==&mid=2648933315&idx=1&sn=4abf2b34a53a85cbd4a3b9ee31fcd398&chksm=88621bfdbf1592eb6a5a13202588dde9068ac7e6391d8dbf3fde33647bf0e044839ba4228216&token=1937580929&lang=zh_CN#rd"> 第9篇：分组查询详解（group by & having）</a>**
10. **<a target="_blank" href="https://mp.weixin.qq.com/s?__biz=MzA5MTkxMDQ4MQ==&mid=2648933321&idx=1&sn=110f6cacb90845bf2327fbcd7acd708c&chksm=88621bf7bf1592e1f1c0f2f01e40d4bd63a48d98daa4ba1157ecad4c6c6520d18b4b2c24c906&token=1096041061&lang=zh_CN#rd"> 第10篇：常用的几十个函数详解</a>**
11. **<a target="_blank" href="https://mp.weixin.qq.com/s?__biz=MzA5MTkxMDQ4MQ==&mid=2648933325&idx=1&sn=33274227db275a3570e1e43ccdd4f49c&chksm=88621bf3bf1592e5b75f537e21961c4295fafb782dbf31c0c1cbf9e36f5f4ed44d31cdad68b3&token=1832579722&lang=zh_CN#rd"> 第11篇：深入了解连接查询及原理</a>**

**mysql系列大概有20多篇，喜欢的请关注一下，欢迎大家加我微信itsoku或者留言交流mysql相关技术!**

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/46/f7310796-ee08-4172-8531-5a44a46285e0.png)

<a style="display:none" target="_blank" href="https://mp.weixin.qq.com/s/_S1DD2JADnXvpexxaBwLLg" style="color:red; font-size:20px; font-weight:bold">继续收门徒，亲手带，月薪 4W 以下的可以来找我</a>

## 最新资料

1. <a href="https://mp.weixin.qq.com/s?__biz=MzkzOTI3Nzc0Mg==&mid=2247484964&idx=2&sn=c81bce2f26015ee0f9632ddc6c67df03&scene=21#wechat_redirect" target="_blank">尚硅谷 Java 学科全套教程（总 207.77GB）</a>
2. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484192&idx=1&sn=505f2faaa4cc911f553850667749bcbb&scene=21#wechat_redirect" target="_blank">2021 最新版 Java 微服务学习线路图 + 视频</a>
3. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484573&idx=1&sn=7f3d83892186c16c57bc0b99f03f1ffd&scene=21#wechat_redirect" target="_blank">阿里技术大佬整理的《Spring 学习笔记.pdf》</a>
4. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484544&idx=2&sn=c1dfe907cfaa5b9ae8e66fc247ccbe84&scene=21#wechat_redirect" target="_blank">阿里大佬的《MySQL 学习笔记高清.pdf》</a>
5. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485167&idx=1&sn=48d75c8e93e748235a3547f34921dfb7&scene=21#wechat_redirect" target="_blank">2021 版 java 高并发常见面试题汇总.pdf</a>
6. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485664&idx=1&sn=435f9f515a8f881642820d7790ad20ce&scene=21#wechat_redirect" target="_blank">Idea 快捷键大全.pdf</a>

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/1/2883e86e-3eff-404a-8943-0066e5e2b454.png)