MySQL中的嵌套查询

2024-10-06 07:06:46

1. 嵌套查询的定义

嵌套查询指在一个查询语句的某个部分嵌入一个子查询。

嵌套查询的执行过程遵循“先子查询、后外层查询”的逻辑。首先，子查询执行并返回一个结果集，可能是一个值、一行或多行数据。接着，外层查询使用子查询的结果继续对数据进行筛选或处理。通过这种方式，嵌套查询可以处理更复杂的逻辑，如多层条件过滤、数据对比等。

  • 子查询：首先执行，返回符合条件的结果。
  • 外层查询：利用子查询返回的结果进行筛选或其他逻辑操作，最终返回结果。

2. 嵌套查询的语法

2.1 嵌套查询的基本结构 

SELECT 列名
FROM 表名
WHERE 列名 比较运算符 (子查询);

先通过子查询返回结果，然后再通过比较运算符判断子查询返回的结果是否满足条件，满足条件的字段的记录，就会展示该记录被SELECT的字段。 

示例 

SELECT column_name1 
FROM table_name1 
WHERE column_name2 比较运算符 (SELECT column_name3 FROM table_name2 WHERE condition);
  •  比较运算符之后的 “(SELECT column_name3 FROM table_name2 WHERE condition)” 是作为子查询，执行SQL语句时，会先选出表table_name2中符合条件condition的记录的column_name3字段给外层查询。
  • 当得到了子查询返回的column_name3字段，外层查询会先将表table_name1的所有记录的column_name2字段 通过比较运算符与这些返回的column_name3字段进行比较。
  • 对于满足了比较运算符规则的column_name2字段的记录，会返回这些记录的column_name1字段。

2.2 常见的比较运算符

  1. =：用于检查外层查询的某个列的值是否等于子查询返回的值。
  2. >、<、>=、<=：用于比较外层查询的列值与子查询结果之间的大小关系。
  3. IN：用于检查外层查询的某个列值是否在子查询返回的一组结果中。
  4. ANY / SOME：用于检查外层查询的列值是否满足子查询返回结果中的任意一个值的条件。
  5. ALL：用于检查外层查询的列值是否满足子查询返回结果中的所有值的条件。

 注意：ANY / SOME和ALL一般要结合>、<、>=、<=来使用

4. ANY / SOME 运算符

SELECT name 
FROM employees 
WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 1);
  • 外层查询筛选出那些薪水大于部门 1 中任意一个员工薪水的员工。
  • 只要大于部门1中薪水最低的员工就符合条件。

5. ALL 运算符

SELECT name 
FROM employees 
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 1);
  • 外层查询筛选出那些薪水大于部门 1 中所有员工薪水的员工。
  • 必须大于部门1中薪水最高的员工才能符合条件。

3. 基于子查询行为的分类 

  • 依据：这一分类基于子查询的返回结果形式以及子查询与外层查询之间的依赖关系。
  • 重点：这一分类更关注子查询本身的性质，即子查询是返回多少数据（单行或多行、多列）、是否依赖于外层查询，以及子查询的执行方式是一次性还是每行重新执行。

3.1 单行子查询

  • 定义：单行子查询是指子查询返回一个字段的一个值。这个值可以是一个具体的数字、日期、文本等。子查询只返回一个结果，外层查询会使用这个结果进行比较、筛选或计算。
  • 特点：子查询返回的是一个字段的单一值。外层查询使用这个值来进行条件判断或筛选。
  • 应用场景：当需要从子查询中获取一个具体的值（如最大值、最小值、平均值等），然后外层查询使用这个值进行比较。

语法结构：

SELECT 列名 
FROM 表名 
WHERE 列名 比较运算符 (子查询);

示例：

SELECT 姓名, 工资 
FROM 员工 
WHERE 工资 = (SELECT MAX(工资) FROM 员工);

解释：

  • 子查询 (SELECT MAX(工资) FROM 员工) 返回员工表中的最高工资。
  • 外层查询根据这个最高工资筛选出符合条件的员工（即工资等于最高工资的员工）。

3.2 多行子查询

  • 定义：多行子查询是指子查询返回一个或多个字段的一个或多个值。外层查询通过集合运算符（如 IN、ANY、ALL 等）来将外层查询的字段与子查询返回的多个结果进行比较和匹配。
  • 特点：子查询返回的是一组值（可以是一个或多个字段），外层查询使用这些值进行筛选或比较。
  • 应用场景：当子查询返回多个值时，外层查询通过集合运算符与这些结果进行匹配。例如，查找某个字段的值是否存在于一组返回值中。

语法结构：

SELECT 列名 
FROM 表名 
WHERE 列名 IN (子查询);

示例：

SELECT 姓名 
FROM 员工 
WHERE 部门编号 IN (SELECT 部门编号 FROM 部门 WHERE 城市 = '上海');

解释：

  • 子查询 (SELECT 部门编号 FROM 部门 WHERE 城市 = '上海') 返回所有位于上海的部门编号，这是一组值。
  • 外层查询使用子查询返回的这些部门编号来筛选出属于这些部门的员工。

3.3 相关子查询

  • 定义：相关子查询是指子查询依赖于外层查询的每一条记录，因此每次外层查询处理新的一条记录时，子查询都会重新执行一次，并根据当前这条记录中的数据计算出结果。这意味着子查询会根据外层查询的变化动态地生成结果。
  • 特点：每当外层查询处理一条新记录时，相关子查询就会根据这条记录的值重新执行，并返回新的结果。子查询的结果因外层查询的记录而动态变化。
  • 应用场景：当子查询的结果依赖于外层查询当前正在处理的记录时使用。例如，针对每条记录计算与其相关的数据或值。

语法结构：

SELECT 列名 
FROM 表名 AS 外层表 
WHERE 列名 比较运算符 (SELECT 列名 FROM 子查询表 WHERE 子查询表.列名 = 外层表.列名);

示例：

SELECT 房屋编号, 价格 
FROM 房屋 AS 可买房屋
WHERE 价格 > (SELECT AVG(价格) 
              FROM 房屋 AS 出售房屋 
              WHERE 出售房屋.城市 = 可买房屋.城市);

解释：

外层查询：

  • SELECT 房屋编号, 价格 FROM 房屋 AS 可买房屋：
    外层查询从 房屋 表中检索每个房屋的编号和价格，并将 房屋 表赋予别名 可买房屋。这个别名的作用是帮助区分外层查询和子查询中的相同表名，以便进行比较。这一步的目的是从所有房屋中找到符合特定条件的房屋。

子查询：

  • (SELECT AVG(价格) FROM 房屋 AS 出售房屋 WHERE 出售房屋.城市 = 可买房屋.城市)：
    子查询的任务是计算当前房屋所在城市的平均房价。子查询也使用了 房屋 表，但被赋予了别名 出售房屋，用于避免与外层查询中的 可买房屋 混淆。子查询的 WHERE 子句指定了只有那些与当前外层查询的房屋处于相同城市的房屋记录才会参与平均价格的计算。

    • 执行过程：每次外层查询处理一条新的房屋记录时，子查询都会根据这条房屋记录的城市，动态地计算该城市中所有房屋的平均价格。也就是说，子查询依赖于外层查询中的房屋城市信息。因此，当外层查询遍历到某个房屋时，子查询会执行一次，计算出这个房屋所在城市的平均房价。

    • 结果作用：子查询返回的是该城市的平均房价。这个值会用于外层查询的 WHERE 子句中，帮助判断当前房屋的价格是否高于其所在城市的平均房价。

整体逻辑：

  1. 子查询的动态执行：对于每一条外层查询的记录（即每一个房屋），子查询都会基于该房屋的城市重新计算城市的平均房价。比如，当外层查询正在处理北京的某个房屋时，子查询会检索所有位于北京的房屋，并计算这些房屋的平均价格。

  2. 条件比较：外层查询的 WHERE 子句会将当前房屋的价格与子查询返回的城市平均房价进行比较。如果当前房屋的价格高于该城市的平均房价，则该房屋的编号和价格会被返回。

3.4 非相关子查询

  • 定义：非相关子查询是指子查询与外层查询没有直接的依赖关系。子查询独立执行一次，返回一个结果，然后外层查询无论处理多少条记录，都只会与这个结果进行比较。
  • 特点：子查询在外层查询之前独立执行一次，返回一个固定值。这个值用于外层查询的每一条记录中进行比较或筛选。
  • 应用场景：当子查询的结果与外层查询无关时使用，例如在查询中用到的某个全局值或固定结果。

语法结构：

SELECT 列名 
FROM 表名 
WHERE 列名 = (子查询);

示例：

SELECT 姓名 
FROM 员工 
WHERE 部门编号 = (SELECT 部门编号 FROM 部门 WHERE 部门名称 = '市场部');

解释：

  • 子查询 (SELECT 部门编号 FROM 部门 WHERE 部门名称 = '市场部') 独立执行一次，返回市场部的部门编号。
  • 外层查询不论处理多少条员工记录，都始终与市场部的编号进行比较。

3.5 总结

  • 单行子查询：返回一个字段的一个值，外层查询与该值进行比较或筛选。
  • 多行子查询：返回一个或多个字段的多个值，外层查询使用集合运算符与这些值进行匹配。
  • 相关子查询：每次外层查询处理新记录时，子查询都会根据该记录的值重新执行，生成新的结果。
  • 非相关子查询：子查询只执行一次，返回固定的结果，外层查询无论处理多少条记录，都与该固定结果进行比较。

4. 基于子查询位置的分类

  • 依据：这一分类基于子查询在 SQL 语句中所处的位置，即子查询是出现在 WHERE、FROM、SELECT 还是 HAVING 子句中。
  • 重点：这一分类关注子查询在外层查询中的用途和功能，即子查询如何与外层查询结合以实现具体的数据处理。

 4.1 WHERE 子句中的嵌套查询

语法：

SELECT 列名
FROM 表名
WHERE 列名 比较运算符 (子查询);

使用场景：

  • WHERE 子句中的嵌套查询常用于条件过滤。通常，嵌套查询返回一个单一值或一组值，然后在外部查询的 WHERE 子句中用作过滤条件。
  • 例如：筛选出满足某些特定条件的记录，如选取工资最高的员工或获取某个特定状态的客户。

示例：

SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Sales'
);

  • 子查询：

    • SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id 用于计算每个员工所在部门的平均工资。
    • 子查询中的 WHERE department_id = employees.department_id 是关键部分，这里表示子查询是针对每个员工所在的部门来计算部门的平均工资。
    • 子查询为每个员工执行一次，返回该员工所在部门的平均工资。

  • 外层查询：

    • 查询员工的姓名和工资 SELECT name, salary FROM employees。
    • WHERE 子句决定筛选条件，要求员工的 salary 大于子查询返回的值。

  • 执行过程：

    • 外层查询对每个员工逐行进行遍历。
    • 对于每个员工，嵌套查询计算其所在部门的平均工资。
    • 然后比较该员工的工资是否高于部门平均工资，只有满足条件的员工会被返回。

4.2 FROM 子句中的嵌套查询

语法：

SELECT 列名
FROM (子查询) AS 临时表

使用场景：

  • FROM 子句中的嵌套查询被称为派生表，类似于创建了一个临时表。它可以简化复杂的聚合操作，特别是当需要多次使用相同的中间结果时。
  • 这种方法常用于对中间结果进行进一步的查询，例如对一个已经聚合的数据集再次进行过滤或计算。

示例：

SELECT department_name, avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS avg_department_salaries
JOIN departments ON avg_department_salaries.department_id = departments.department_id;

  • 子查询：

    • 子查询 SELECT department_id, salary FROM employees 提取所有员工的部门ID和工资，形成了一个虚拟表 dept_employees。
    • 这个虚拟表作为数据源传递给外层查询，就像一个普通的表一样。

  • 外层查询：

    • 外层查询的 SELECT department_id, AVG(salary) AS average_salary, COUNT(*) AS num_employees 负责从 dept_employees 中对数据进行处理。
    • AVG(salary) 计算每个部门的平均工资。
    • COUNT(*) 计算每个部门的员工数。

  • 执行过程：

    • 子查询生成一个仅包含 department_id 和 salary 列的临时表 dept_employees。
    • 外层查询对这个临时表的数据进行分组，并计算每个部门的员工数量和平均工资。

4.3 SELECT 子句中的嵌套查询

语法：

SELECT (子查询) AS 列名
FROM 表名;

使用场景：

  • SELECT 子句中的嵌套查询常用于动态生成新的列。这些子查询通常为每一行返回一个计算结果，用于丰富原始数据集。
  • 这种方式通常用于统计计算、数据转换，或者从其他表中提取额外的信息。

示例：

SELECT employee_id, first_name, last_name,
    (SELECT department_name 
     FROM departments 
     WHERE departments.department_id = employees.department_id) AS department_name
FROM employees;

  • 子查询：

    • 子查询 SELECT MAX(degree) FROM education WHERE education.employee_id = employees.id 用于查找与当前员工对应的最高学历。
    • WHERE education.employee_id = employees.id 确保子查询与外层查询中的每个员工相匹配。
    • 子查询返回当前员工的最高学历。

  • 外层查询：

    • 外层查询 SELECT name, ... FROM employees 检索所有员工的姓名。
    • highest_degree 列使用子查询的结果作为额外的信息。

  • 执行过程：

    • 对于外层查询中的每个员工，子查询会查找其最高学历，并将其作为外层查询的结果列 highest_degree。
    • 每行执行一次子查询，因此每个员工的最高学历都会与员工姓名一起返回。

4.4 HAVING 子句中的嵌套查询

语法：

SELECT 列名
FROM 表名
GROUP BY 列名
HAVING 聚合函数 比较运算符 (子查询);

使用场景：

  • HAVING 子句中的嵌套查询通常用于在分组后的数据基础上进行复杂的过滤。HAVING 是对聚合结果（如 SUM、COUNT、AVG 等）的过滤，嵌套查询可以用于比较每个分组的结果与其他表的数据或特定计算值。
  • 常见场景是当我们需要对聚合后的结果集进行精细化的过滤时，例如筛选出满足某一条件的分组。

示例：

SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > (SELECT AVG(employee_count) FROM (SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id) AS dept_counts);

  • 子查询：

    • 子查询 SELECT AVG(employee_count) FROM (SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id) AS dept_counts 首先计算每个部门的员工数量，然后计算这些数量的平均值。
    • 这其实是一个双重嵌套查询，第一层子查询计算每个部门的员工数量，第二层子查询计算这些员工数量的平均值。

  • 外层查询：

    • SELECT department_id FROM employees GROUP BY department_id 对员工按部门进行分组。
    • HAVING COUNT(*) > ... 是一个过滤条件，用于过滤掉员工人数不满足条件的部门。

  • 执行过程：

    • 外层查询首先按部门分组，计算每个部门的员工数量。
    • 子查询计算所有部门的平均员工数量。
    • HAVING 子句确保只返回那些员工数量大于平均值的部门。

4.5 总结

  • WHERE 子句中的嵌套查询：用于动态过滤外层查询的行，基于子查询的结果进行比较。
  • FROM 子句中的嵌套查询：创建临时表，允许在外层查询中使用简化的数据集进行进一步的操作。
  • SELECT 子句中的嵌套查询：生成动态列值，将子查询的结果直接应用到外层查询的每一行。
  • HAVING 子句中的嵌套查询：用于基于聚合结果进行分组后的过滤，是处理复杂分组统计场景的有效手段。

5. 嵌套查询的性能考虑

5.1 嵌套查询对性能的影响因素

嵌套查询，尤其是复杂的嵌套查询，可能对数据库性能产生显著的影响。以下是影响性能的主要因素：

  • 查询的重复执行：

    • 对于相关子查询，每处理一条外层查询的记录，子查询都会执行一次。这样，子查询的执行次数与外层查询的记录数成正比，导致性能下降，特别是在处理大量数据时。
    • 非相关子查询相对更快，因为子查询只执行一次，结果存储后供外层查询使用，但如果子查询本身很复杂或数据量很大，性能也会受到影响。

  • 索引的利用情况：

    • 嵌套查询中，如果子查询和外层查询没有正确地使用索引，数据库可能需要进行大量的全表扫描（Full Table Scan）。这会导致较大的 I/O 负担，进而影响性能。

  • 查询的复杂性：

    • 嵌套查询往往涉及多个表的联结、分组、排序等操作，复杂的逻辑可能导致数据库查询计划（Query Plan）变得更复杂，增加了查询的处理时间。

5.2 优化嵌套查询的方法

  • 使用 JOIN 替代子查询：

    • 如果可以，将嵌套查询转换为 JOIN 查询。JOIN 查询通常更容易优化，数据库可以更高效地处理连接操作。例如，子查询可以被重写为 INNER JOIN 或 LEFT JOIN，这通常会显著提高性能。

  • 索引优化：

    • 在参与嵌套查询的字段上创建索引，尤其是用于过滤条件的字段。例如，在 WHERE 子句中出现的字段，如果有适当的索引，可以显著提高查询速度。

  • 避免相关子查询：

    • 尽可能避免使用相关子查询，因为相关子查询会为外层查询的每条记录执行一次子查询，效率较低。可以尝试将相关子查询重写为非相关子查询或连接查询。

  • 分解复杂查询：

    • 将复杂的嵌套查询分解为多个简单的查询，使用临时表或视图保存中间结果。这种做法有时可以简化数据库的处理逻辑，提升性能。

6. 嵌套查询的优势与劣势

优势

  • 功能强大：嵌套查询能够处理复杂的业务逻辑，尤其是当需要在同一个查询中进行多个独立计算时（如计算聚合值、条件筛选等）。
  • 代码结构简洁：某些情况下，嵌套查询可以避免使用中间结果或多次查询，代码逻辑更加紧凑。
  • 动态筛选：嵌套查询可以动态计算出结果，适合处理依赖于其他结果的数据操作。

劣势

  • 性能问题：嵌套查询，特别是相关子查询，可能带来性能问题，尤其是在大数据集上运行时效率较低。
  • 可读性差：多层嵌套查询会导致查询逻辑复杂、代码难以理解，维护和调试变得困难。
  • 数据库支持差异：不同数据库对嵌套查询的优化程度不一样，在某些数据库中嵌套查询的表现不佳，可能需要转换为其他查询方式。
上一篇：JSP(Java Server Pages)基础使用三(表单传参)


下一篇：云原生（四十八） | Nginx软件安装部署