【重学 MySQL】四十四、相关子查询

【重学 MySQL】四十四、相关子查询

  • 相关子查询执行流程
  • 示例
    • 使用相关子查询进行过滤
    • 使用相关子查询进行存在性检查
    • 使用相关子查询进行计算
  • 在 `select`，`from`，`where`，`having`，`order by` 中使用相关子查询举例
    • `SELECT` 子句中使用相关子查询
    • `FROM` 子句中使用相关子查询
    • `WHERE` 子句中使用相关子查询
    • `HAVING` 子句中使用相关子查询
    • `ORDER BY` 子句中使用相关子查询
    • 总结
  • `EXISTS` 和 `NOT EXISTS`
    • `EXISTS`
    • `NOT EXISTS`
    • 关键点
  • 注意事项
  • 替代方法

在这里插入图片描述
在 MySQL 中，相关子查询（也称为相关子查询或关联子查询）是一种特殊类型的子查询，其执行依赖于外部查询的当前行值。这意味着相关子查询在外部查询的每一行上都会重新执行一次，并且可以使用外部查询的列值。

相关子查询执行流程

相关子查询的执行流程涉及多个步骤，并且这些步骤在数据库管理系统（DBMS）中是高度优化的。

  1. 解析和优化：

    • 数据库管理系统首先解析SQL语句，包括相关子查询，以确保其符合语法规则。
    • 接着，系统进行语义解析，检查表名、列名、数据类型、权限等约束条件是否满足。
    • 对于包含相关子查询的查询语句，DBMS会尝试找到最优的查询计划，以便快速地从数据库中检索所需的数据。这包括选择最佳的索引、使用缓存和预处理语句等优化措施。

  2. 生成执行计划：

    • 在查询优化后，系统会生成一个执行计划，该计划描述了如何获取查询结果，包括访问哪些表、采用哪些索引、如何连接各个表等。
    • 对于相关子查询，执行计划会考虑子查询与外部查询之间的依赖关系，并确定子查询的执行时机和方式。

  3. 执行外部查询：

    • 外部查询（即包含相关子查询的查询）开始执行。在外部查询的每一行处理过程中，都会涉及到相关子查询的执行。

  4. 执行相关子查询：

    • 对于外部查询中的每一行，DBMS都会执行一次相关子查询。
    • 相关子查询依赖于外部查询的当前行值。这意味着，每次外部查询处理一行数据时，子查询都会使用该行数据中的值作为条件来执行。
    • 子查询的结果通常用于过滤、排序或作为外部查询的一部分进行计算。

  5. 组合结果：

    • 外部查询根据子查询的结果来处理每一行数据，并生成最终的查询结果集。
    • 如果子查询返回多个结果，外部查询可能会使用这些结果来进行进一步的过滤或计算。

  6. 返回结果：

    • 最后，数据库将查询结果集返回给客户端应用程序。

需要注意的是，相关子查询可能会导致性能问题，因为对于外部查询返回的每一行数据，数据库都需要重新执行子查询。因此，在编写包含相关子查询的SQL语句时，应谨慎考虑其性能影响，并尝试使用其他优化技术（如索引、连接优化、窗口函数等）来提高查询效率。

此外，虽然相关子查询在某些情况下非常有用，但在其他情况下，使用连接（JOIN）操作或窗口函数可能更加高效和直观。因此，在选择使用哪种查询技术时，应根据具体需求和性能考虑做出决策。

示例

使用相关子查询进行过滤

假设我们有两个表：employees（员工）和 departments（部门）。我们想要找到每个部门中工资最高的员工。

SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary = (SELECT MAX(sub.salary)FROM employees subWHERE sub.department_id = e.department_id
);

在这个查询中，子查询 SELECT MAX(sub.salary) FROM employees sub WHERE sub.department_id = e.department_id 是一个相关子查询，因为它依赖于外部查询的 e.department_id。

使用相关子查询进行存在性检查

假设我们有两个表：students（学生）和 courses（课程）。我们想要找到那些选修了所有课程的学生。

SELECT s.name
FROM students s
WHERE NOT EXISTS (SELECT c.course_idFROM courses cWHERE NOT EXISTS (SELECT 1FROM enrollments eWHERE e.student_id = s.student_id AND e.course_id = c.course_id)
);

在这个查询中，内部子查询 SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id AND e.course_id = c.course_id 是一个相关子查询，它依赖于外部子查询的 c.course_id 和外部查询的 s.student_id。

使用相关子查询进行计算

假设我们有一个表 sales，其中包含每个销售员的销售记录。我们想要计算每个销售员的销售总额，并找出销售额超过该销售员平均销售额的记录。

SELECT s.salesperson_id, s.sale_amount
FROM sales s
WHERE s.sale_amount > (SELECT AVG(sub.sale_amount)FROM sales subWHERE sub.salesperson_id = s.salesperson_id
);

在这个查询中，子查询 SELECT AVG(sub.sale_amount) FROM sales sub WHERE sub.salesperson_id = s.salesperson_id 是一个相关子查询，因为它依赖于外部查询的 s.salesperson_id。

在 select，from，where，having，order by 中使用相关子查询举例

在SQL查询中，相关子查询（也称为相关子选择或相关嵌套查询）是指依赖于外部查询中的值的子查询。它们通常用于在SELECT、FROM、WHERE、HAVING和ORDER BY子句中实现复杂的逻辑。以下是一些示例，展示了如何在这些子句中使用相关子查询。

SELECT 子句中使用相关子查询

虽然直接在SELECT子句中使用相关子查询不太常见，但你可以通过派生表（子查询作为表）间接实现。不过，这里展示一个更直接的场景，即在SELECT中嵌入相关子查询作为计算列。

SELECT employee_id,first_name,last_name,(SELECT COUNT(*) FROM orders WHERE orders.employee_id = employees.employee_id) AS order_count
FROM employees;

这个查询为每个员工返回了一个订单计数。

FROM 子句中使用相关子查询

在FROM子句中使用相关子查询通常通过派生表（子查询作为临时表）来实现，但相关子查询在这种场景下不常见。然而，你可以通过JOIN与WHERE条件实现类似的效果。

select e.last_name, e.salary, e.department_id
from employees e, (select department_id, avg(salary) avg_salaryfrom employeesgroup by department_id) t_dept_avg_salary
where e.department_id   = t_dept_avg_salary.department_idand e.salary > t_dept_avg_salary.avg_salary;

它使用了隐式内连接（也称为笛卡尔积加过滤）来比较每个员工的工资与其所在部门的平均工资。这里，您创建了一个派生表（也称为子查询或临时表）t_dept_avg_salary，该表包含了每个部门的平均工资。然后，您将这个派生表与employees表连接起来，以便比较每个员工的工资与其部门的平均工资。

  1. 派生表 t_dept_avg_salary：

    (select department_id, avg(salary) avg_salaryfrom employeesgroup by department_id)

    这个子查询从employees表中计算每个部门的平均工资，并将结果作为一个临时表（派生表）。这个表有两列：department_id（部门ID）和avg_salary（该部门的平均工资）。

  2. 主查询：

    select e.last_name, e.salary, e.department_id
from employees e, (子查询) t_dept_avg_salary
where e.department_id = t_dept_avg_salary.department_idand e.salary > t_dept_avg_salary.avg_salary;

    主查询从employees表（别名为e）和派生表t_dept_avg_salary中选择数据。它通过department_id将这两个表连接起来，并过滤出那些工资高于其部门平均工资的员工。

  3. 结果：
    查询结果将包含那些工资高于其所在部门平均工资的员工的姓氏（last_name）、工资（salary）和部门ID（department_id）。

虽然您的查询在功能上是正确的，但现代SQL风格通常推荐使用显式的JOIN语法来替代隐式连接，因为它更清晰且更易于维护。以下是使用显式JOIN的等效查询：

SELECT e.last_name, e.salary, e.department_id
FROM employees e
JOIN (SELECT department_id, AVG(salary) AS avg_salaryFROM employeesGROUP BY department_id
) t_dept_avg_salary ON e.department_id = t_dept_avg_salary.department_id
WHERE e.salary > t_dept_avg_salary.avg_salary;

这个查询与您的原始查询在逻辑上是相同的，但使用了显式的JOIN语法，这通常被认为是更好的做法。

WHERE 子句中使用相关子查询

在WHERE子句中使用相关子查询非常常见，用于过滤记录。

SELECT employee_id,first_name,last_name
FROM employees e
WHERE (SELECT COUNT(*) FROM orders o WHERE o.employee_id = e.employee_id) > 5;

这个查询返回了订单数量超过5的员工。

HAVING 子句中使用相关子查询

HAVING子句通常用于聚合查询的过滤，但在HAVING中使用相关子查询的情况较少。这里通过一个例子展示如何在HAVING中嵌入相关子查询。

SELECT department_id,COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > (SELECT AVG(emp_count) FROM (SELECT COUNT(employee_id) AS emp_count FROM employees GROUP BY department_id) AS avg_dept_counts);

这个查询返回了员工数量超过所有部门平均员工数量的部门。

ORDER BY 子句中使用相关子查询

在ORDER BY子句中使用相关子查询的情况也不常见，但可以通过派生表或窗口函数实现类似效果。不过，直接嵌入相关子查询也可以在某些特殊情况下使用。

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

这个查询首先按部门平均工资降序排序，然后按员工个人工资降序排序。

总结

相关子查询在SQL查询中非常强大，可以用于实现复杂的逻辑。然而，它们可能会降低查询性能，特别是在处理大量数据时。因此，在使用相关子查询时，应考虑其性能影响，并考虑使用其他优化技术，如索引、连接优化或窗口函数等。

EXISTS 和 NOT EXISTS

EXISTS 和 NOT EXISTS 是 SQL 中用于测试子查询是否返回任何行的条件运算符。它们通常用于在 WHERE 子句或 HAVING 子句中，以确定是否满足某个条件，从而决定是否包含某些行在结果集中。

EXISTS

EXISTS 运算符用于测试子查询是否返回至少一行。如果子查询返回一行或多行，EXISTS 条件就为真（TRUE），否则为假（FALSE）。

示例：

SELECT first_name, last_name
FROM employees e
WHERE EXISTS (SELECT 1FROM departments dWHERE e.department_id = d.department_idAND d.department_name = 'Sales'
);

这个查询返回了所有在名为 ‘Sales’ 的部门工作的员工的名字。子查询检查是否存在至少一个部门，其 department_id 与 employees 表中的 department_id 匹配，并且部门名称为 ‘Sales’。

NOT EXISTS

NOT EXISTS 运算符用于测试子查询是否不返回任何行。如果子查询没有返回任何行，NOT EXISTS 条件就为真（TRUE），否则为假（FALSE）。

示例：

SELECT first_name, last_name
FROM employees e
WHERE NOT EXISTS (SELECT 1FROM departments dWHERE e.department_id = d.department_idAND d.department_name = 'HR'
);

这个查询返回了所有不在名为 ‘HR’ 的部门工作的员工的名字。子查询检查是否不存在任何部门，其 department_id 与 employees 表中的 department_id 匹配，并且部门名称为 ‘HR’。

关键点

  • EXISTS 和 NOT EXISTS 子查询通常只关心是否存在行，而不关心行的具体内容。因此，子查询中的 SELECT 子句经常简单地选择常量（如 SELECT 1），因为实际选择的列并不重要。
  • 这些运算符通常比使用 IN、NOT IN、JOIN（在某些情况下）等替代方法更高效，特别是当子查询可能返回大量行时。
  • 使用 EXISTS 和 NOT EXISTS 时，应确保子查询中的条件能够正确地反映你想要测试的逻辑。
  • 在某些数据库系统中，EXISTS 和 NOT EXISTS 可能会利用索引来优化查询性能。因此，在设计数据库和编写查询时，考虑索引的使用是很重要的。

注意事项

  1. 性能问题：由于相关子查询在外部查询的每一行上都会重新执行，因此可能会导致性能问题，特别是在处理大数据集时。在这种情况下，可以考虑使用 JOIN 或其他优化技术。

  2. 可读性：相关子查询有时可能使查询变得难以理解和维护。因此，在编写复杂查询时，确保代码清晰并添加适当的注释。

  3. 索引：确保在相关子查询中使用的列上建立适当的索引，以提高查询性能。

替代方法

在某些情况下，可以使用 JOIN 或窗口函数（MySQL 8.0+ 支持）来替代相关子查询，从而获得更好的性能和可读性。例如，上面的第一个示例（找到每个部门中工资最高的员工）可以使用 JOIN 和 GROUP BY 来重写：

SELECT e1.name, e1.salary, e1.department_id
FROM employees e1
JOIN (SELECT department_id, MAX(salary) AS max_salaryFROM employeesGROUP BY department_id
) e2 ON e1.department_id = e2.department_id AND e1.salary = e2.max_salary;

这种重写方式通常更高效，因为它避免了相关子查询的重复执行。

通过理解和使用相关子查询，你可以解决一些复杂的查询问题。然而，要注意性能问题，并考虑使用其他技术来优化查询。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/442886.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

刷题 -哈希

刷题 -哈希

面试面试经典 150 题 - 哈希 383. 赎金信 - 一个哈希表搞定 class Solution { public:bool canConstruct(string ransomNote, string magazine) {int hash[26] {0};for (auto& ch : magazine) {hash[ch - a];}for (auto& ch : ransomNote) {if (--hash[ch - a] < …
阅读更多...
Linux的六个入侵检查思路及预防

Linux的六个入侵检查思路及预防

背景 入侵检查是保障计算机安全运行的重要手段之一&#xff0c; 通过操作系统的静态配置分析、日志分析、异常行为分析以及文件完整性等方式来做检查&#xff0c;来判断我们的操作系统是否有受到入侵。今天阿祥就介绍十个简单的入侵检查思路及应对措施&#xff0c;希望对大家有…
阅读更多...
原生USDC正式上线Sui

原生USDC正式上线Sui

今天&#xff0c;标志着Sui生态的一个重要里程碑 — — 原生USDC现已正式在Sui主网上线。作为最广泛使用的稳定币之一&#xff0c;USDC为日益增长的Sui生态带来了稳定的价值传输和流动性。 随着Sui DeFi锁仓量&#xff08;TVL&#xff09;突破10亿美元&#xff0c;网络上需要更…
阅读更多...
Linux同时安装多个JDK

Linux同时安装多个JDK

Linux同时安装多个JDK 一、JDK1.1、JDK的下载1.2、解压并放置目录 二、通过alias切换版本2.1、修改profile文件2.2、使用和验证 三、使用update-alternatives工具3.1、修改profile文件3.2、指定JDK版本3.3、使用和验证 四、总结 一、JDK 1.1、JDK的下载 JDK官网下载&#xff…
阅读更多...
无人机之飞行算法篇

无人机之飞行算法篇

无人机的飞行算法是一个复杂而精细的系统&#xff0c;它涵盖了多个关键技术和算法&#xff0c;以确保无人机能够稳定、准确地执行飞行任务。 一、位置估计 无人机在空中飞行过程中需要实时获取其位置信息&#xff0c;以便进行路径规划和控制。这通常通过以下传感器实现&#…
阅读更多...
Rust编程中的循环语句

Rust编程中的循环语句

【图书介绍】《Rust编程与项目实战》-CSDN博客 《Rust编程与项目实战》(朱文伟&#xff0c;李建英)【摘要 书评 试读】- 京东图书 (jd.com) Rust编程与项目实战_夏天又到了的博客-CSDN博客 6.2 for 循 环 迭代次数是确定/固定的循环称为确定循环。for 循环是一个确定循环…
阅读更多...
新书速览|你好，C++

新书速览|你好，C++

《你好&#xff0c;C》 本书内容 《你好&#xff0c;C》主要介绍C开发环境的搭建、基础语法知识、面向对象编程思想以及标准模板库的应用&#xff0c;特别针对初学者在学习C过程中可能遇到的难点提供了解决方案。全书共分13章&#xff0c;以一个工资程序的不断优化和完善为线索…
阅读更多...
速度白嫖：Minimax海螺上线图生视频功能

速度白嫖：Minimax海螺上线图生视频功能

一、什么是Minimax海螺 网址&#xff1a;https://hailuoai.video/ Minimax海螺是一款创新的内容创作工具&#xff0c;专注于将静态图像转化为动态视频。它利用先进的图像处理与生成算法&#xff0c;帮助用户将普通图片迅速转变为引人入胜的短视频&#xff0c;适合社交媒体、…
阅读更多...
【HarmonyOS开发笔记 1】 -- 开发环境的搭建

【HarmonyOS开发笔记 1】 -- 开发环境的搭建

DevEco Studio 的下载与安装 下载 下载路径&#xff1a; https://developer.huawei.com/consumer/cn/download/ 安装 解压后双击 deveco-studio-5.0.3.814.exe 指定安装目录&#xff0c;或者默认&#xff0c;然后下一步 一直“下一步”&#xff0c; 直到最后安装完成 新…
阅读更多...
视频消重pr模板|胶片损伤特效视频去重pr模板工程文件

视频消重pr模板|胶片损伤特效视频去重pr模板工程文件

可以用于视频消重效果的pr去重模板&#xff0c;10种胶片损伤特效视频叠加素材pr工程文件。 Premiere Pro模板&#xff0c;可以使用这些效果来增强您的媒体。音乐不包括在内。 下载地址&#xff1a;Pr模板网 下载链接&#xff1a;https://prmuban.com/40591.html
阅读更多...
分享我“Excel 表格”关键字的博客笔记（python脚本全程自动）

分享我“Excel 表格”关键字的博客笔记（python脚本全程自动）

Python脚本全程自动&#xff0c;全部Python内建工具脚本纯净。 (笔记模板由python脚本于2024年10月05日 19:51:06创建&#xff0c;本篇笔记适合喜欢Excel和Python的coder翻阅) 【学习的细节是欢悦的历程】 Python 官网&#xff1a;https://www.python.org/ Free&#xff1a;大…
阅读更多...
Qt - QMenu

Qt - QMenu

QMenu 1、menu转string输出 //GlobalEnum.h #include <QObject> #include <QMetaEnum> class GlobalEnum : public QObject {Q_OBJECT public:EnumTest();enum Enum_Test{ZhangSan 0,WangWu,};Q_ENUM(Enum_Test) };#define EnumToString(e) \ QMetaEnum::fromTy…
阅读更多...
手把手教你如何配置好VS Code的WEB基础开发环境（保姆级）

手把手教你如何配置好VS Code的WEB基础开发环境（保姆级）

1. VS Code介绍 微软旗下的多场景开发环境软件&#xff0c;支持JAVA、C、C#、C、WEB、VUE、CSS、HTML、Python等等等 如果你刚刚开始编程或者准备学习WEB&#xff0c;那么我强烈建议你使用这款软件 缺点&#xff1a;&#xff08;针对初学者&#xff09; 需要安装各种各样的插…
阅读更多...
数据分析-29-基于pandas的窗口操作和对JSON格式数据的处理

数据分析-29-基于pandas的窗口操作和对JSON格式数据的处理

文章目录 1 窗口操作1.1 滑动窗口思想1.2 函数df.rolling2 JSON格式数据2.1 处理简单JSON对象和JSON列表2.1.1 处理简单的JSON结构2.1.2 处理空字段2.1.3 获取部分字段2.2 处理多级json2.2.1 展开所有级别(默认)2.2.2 自定义展开层级2.3 处理嵌套列表JSON3 参考附录1 窗口操作 …
阅读更多...
每日学习一个数据结构-图

每日学习一个数据结构-图

文章目录 图基础一、图的定义二、图的相关概念三、图的分类四、图的使用场景 和图相关的算法一、图的遍历算法二、最短路径算法三、最小生成树算法四、图匹配算法五、网络流算法 图基础 一、图的定义 在数学中&#xff0c;图是描述于一组对象的结构&#xff0c;其中某些对象对…
阅读更多...
YOLOv11模型地址

YOLOv11模型地址

地址链接 项目Git地址&#xff1a;https://github.com/ultralytics/ultralytics?tabreadme-ov-file
阅读更多...
大模型生成PPT大纲优化方案：基于 nVidia NIM 平台的递归结构化生成

大模型生成PPT大纲优化方案：基于 nVidia NIM 平台的递归结构化生成

大模型生成PPT大纲优化方案&#xff1a;基于 nVidia NIM 平台的递归结构化生成 待解决的问题 生成PPT大纲是一种大模型在办公场景下应用的常见需求。 然而&#xff1a; 目前直接让大模型生成大纲往往是非结构化的&#xff0c;输出格式多样&#xff0c;难以统一和规范&#…
阅读更多...
Idea 2024.2.3 找不到Cache Recovery设置

Idea 2024.2.3 找不到Cache Recovery设置

idea找不到官网所说的设置 下面是解决办法 1.找到对应位置 2.增加配置文件内容 idea.is.internaltrue3.重启idea 4.查看结果 解决方案原文
阅读更多...
Kubernetes（K8s）的简介

Kubernetes（K8s）的简介

一、Kubernetes的简介 1 应用部署方式演变 在部署应用程序的方式上&#xff0c;主要经历了三个阶段&#xff1a; 传统部署&#xff1a;互联网早期&#xff0c;会直接将应用程序部署在物理机上 优点&#xff1a;简单&#xff0c;不需要其它技术的参与 缺点&#xff1a;不能为应…
阅读更多...
MySQL 查询数据

MySQL 查询数据

MySQL 数据库使用SQL SELECT语句来查询数据。 你可以通过 mysql> 命令提示窗口中在数据库中查询数据&#xff0c;或者通过PHP脚本来查询数据。 语法 以下为在MySQL数据库中查询数据通用的 SELECT 语法&#xff1a; SELECT column_name,column_name FROM table_name [WHER…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023