子查询

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

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

子查询分类

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

• 标量子查询（结果集只有一行一列）

• 列子查询（结果集只有一列多行）

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

• 表子查询（结果集一般为多行多列）

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

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

• from后面：支持表子查询。

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

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

准备测试数据

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

浏览器中打开链接：http://www.itsoku.com/article/209

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

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

select后面的子查询

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

示例1

查询每个部门员工个数

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

示例2

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

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

from后面的子查询

将子查询的结果集充当一张表，要求必须起别名，否者这个表找不到。

然后将真实的表和子查询结果表进行连接查询。

示例1

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

-- 查询每个部门平均工资SELECT
  department_id,
  avg(a.salary)FROM employees aGROUP BY a.department_id;-- 薪资等级表SELECT *FROM job_grades;-- 将上面2个结果连接查询，筛选条件:平均工资 between lowest_sal and highest_sal;SELECT
  t1.department_id,
  sa AS '平均工资',
  t2.grade_levelFROM (SELECT
        department_id,
        avg(a.salary) sa      FROM employees a      GROUP BY a.department_id) t1, job_grades t2WHERE
  t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;1234567891011121314151617181920212223

运行最后一条结果如下：

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)12345678910111213141516171819202122232425262728

where和having后面的子查询

where或having后面，可以使用

• 标量子查询（单行单列行子查询）

• 列子查询（单列多行子查询）

• 行子查询（多行多列）

特点

• 子查询放在小括号内。

• 子查询一般放在条件的右侧。

• 标量子查询，一般搭配着单行操作符使用，多行操作符 >、<、>=、<=、=、<>、!=

• 列子查询，一般搭配着多行操作符使用

1. in(not in)：列表中的“任意一个”

2. any或者some：和子查询返回的“某一个值”比较，比如a>som(10,20,30)，a大于子查询中任意一个即可，a大于子查询中最小值即可，等同于a>min(10,20,30)。

3. all：和子查询返回的“所有值”比较，比如a>all(10,20,30)，a大于子查询中所有值，换句话说，a大于子查询中最大值即可满足查询条件，等同于a>max(10,20,30);

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

mysql中的in、any、some、all

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

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

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

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

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

标量子查询

一般标量子查询，示例
查询谁的工资比Abel的高？

/*①查询abel的工资【改查询是标量子查询】*/SELECT salaryFROM employeesWHERE last_name = 'Abel';/*②查询员工信息，满足salary>①的结果*/SELECT *FROM employees aWHERE a.salary > (SELECT salary                  FROM employees                  WHERE last_name = 'Abel');1234567891011

多个标量子查询，示例

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

/*返回job_id与141号员工相同，salary比143号员工多的员工、姓名、job_id和工资*//*①查询141号员工的job_id*/SELECT job_idFROM employeesWHERE employee_id = 141;/*②查询143好员工的salary*/SELECT salaryFROM employeesWHERE employee_id = 143;/*③查询员工的姓名、job_id、工资，要求job_id=① and salary>②*/SELECT
  a.last_name 姓名,
  a.job_id,
  a.salary    工资FROM employees aWHERE a.job_id = (SELECT job_id                  FROM employees                  WHERE employee_id = 141)
      AND
      a.salary > (SELECT salary                  FROM employees                  WHERE employee_id = 143);12345678910111213141516171819202122

子查询+分组函数，示例

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

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

错误的标量子查询，示例 http://dxb.myzx.cn

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

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 row123456789

错误提示：子查询返回的结果超过了1行记录。

说明：上面的子查询只支持最多一列一行记录。

列子查询

列子查询需要搭配多行操作符使用：in(not in)、any/some、all。

为了提升效率，最好去重一下distinct关键字。

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

/*返回location_id是1400或1700的部门中的所有员工姓名*//*方式1*//*①查询location_id是1400或1700的部门编号*/SELECT DISTINCT department_idFROM departmentsWHERE location_id IN (1400, 1700);/*②查询员工姓名，要求部门是①列表中的某一个*/SELECT a.last_nameFROM employees aWHERE a.department_id IN (SELECT DISTINCT department_id                          FROM departments                          WHERE location_id IN (1400, 1700));/*方式2：使用any实现*/SELECT a.last_nameFROM employees aWHERE a.department_id = ANY (SELECT DISTINCT department_id                             FROM departments                             WHERE location_id IN (1400, 1700));/*拓展，下面与not in等价*/SELECT a.last_nameFROM employees aWHERE a.department_id <> ALL (SELECT DISTINCT department_id                             FROM departments                             WHERE location_id IN (1400, 1700));123456789101112131415161718192021222324252627

示例2
返回其他工种中比job_id为’IT_PROG’工种任意工资低的员工的员工号、姓名、job_id、salary

/*返回其他工种中比job_id为'IT_PROG'工种任一工资低的员工的员工号、姓名、job_id、salary*//*①查询job_id为'IT_PROG'部门任-工资*/SELECT DISTINCT salaryFROM employeesWHERE job_id = 'IT_PROG';/*②查询员工号、姓名、job_id、salary，slary<①的任意一个*/SELECT
  last_name,
  employee_id,
  job_id,
  salaryFROM employeesWHERE salary < ANY (SELECT DISTINCT salary                    FROM employees                    WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';/*或者*/SELECT
  last_name,
  employee_id,
  job_id,
  salaryFROM employeesWHERE salary < (SELECT max(salary)
                FROM employees                WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';123456789101112131415161718192021222324252627

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

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

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

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

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

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

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

语法：exists(玩转的查询语句)。 http://ask.baikezh.com/xian/

• exists查询结果：1或0，exists查询的结果用来判断子查询的结果集中是否有值。

• 一般来说，能用exists的子查询，绝对都能用in代替，所以exists用的少。

• 和前面的查询不同，这先执行主查询，然后主查询查询的结果，在根据子查询进行过滤，子查询中涉及到主查询中用到的字段，所以叫相关子查询。

示例1
简单示例

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)123456789

示例2
查询所有员工的部门名称

/*exists入门案例*/SELECT exists(SELECT employee_id              FROM employees              WHERE salary = 300000) AS 'exists返回1或者0';/*查询所有员工部门名*/SELECT department_nameFROM departments aWHERE exists(SELECT 1
             FROM employees b             WHERE a.department_id = b.department_id);/*使用in实现*/SELECT department_nameFROM departments aWHERE a.department_id IN (SELECT department_id                          FROM employees);1234567891011121314151617

示例3
查询没有员工的部门 http://ask.baikezh.com/

/*查询没有员工的部门*//*exists实现*/SELECT *FROM departments aWHERE 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 aWHERE a.department_id NOT IN (SELECT department_id                              FROM employees b                              WHERE b.department_id IS NOT NULL);12345678910111213

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

NULL的大坑

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

SELECT *FROM departments aWHERE a.department_id NOT IN (SELECT department_id                              FROM employees b);1234

运行结果：

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

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

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