MySQL 关键字 IN 与 EXISTS 的使用与区别

news/2024/8/26 15:59:50 标签: mysql, 数据库, SQL语句

目录

  • 1. IN & NOT IN
    • 1.1 基本使用
    • 1.2 工作原理
      • 1.2.1 静态值列表的 IN 语句
      • 1.2.2 子查询的 IN 语句
    • 1.3 相关优化
  • 2. EXISTS & NOT EXISTS
    • 2.1 基本使用
    • 2.2 工作原理
    • 2.3 相关优化
  • 3. 两者区别

1. IN & NOT IN

IN：IN用于判断某个字段的值是否存在于给定的值列表中，常用于简单的列表匹配。可以使用单个值，也可以使用一个由多个值组成的列表，也可以是一个子查询。以下是IN关键词的示例用法：

1.1 基本使用

  • 📓 语法一：
SELECT * FROM table_name 
WHERE column_name IN (value1, value2, value3);

-- 示例
SELECT * FROM employees WHERE department_id IN (1, 2, 3);

这将返回table_name表中满足条件的行，其中列column_name的值在给定的值列表(value1, value2, value3)中。

  • 📓 语法二：
SELECT * FROM table_name 
WHERE column_name IN (select column_name_b from table_name_b  WHERE condition );

-- 示例
SELECT * FROM employees WHERE department_id IN 
	(SELECT id FROM departments WHERE name = 'WorkDog');

其实与上面的用法是一样的，只是将给定的值列表换成了 table_name_b 表中的某个字段的值。先查出对应字段的所有值，然后再与前面表 table_name 的 column_name 字段进行值比较，返回table_name表中满足条件的行。

1.2 工作原理

在MySQL中，IN 语句用于检查某个值是否在指定的列表或子查询结果集中。IN 语句的工作原理包括处理静态值列表和子查询结果集。在不同的情况下，MySQL会采用不同的策略来执行 IN 语句。下面详细解释 IN 语句的工作原理。

1.2.1 静态值列表的 IN 语句

对于静态值列表，MySQL会将列表中的每个值与目标列的值进行比较。如果目标值在列表中，条件为真。

📓 假设有两个表 employees ，查询指定 department_id 的部门：

SELECT * 
FROM employees 
WHERE department_id IN (1, 2, 3);

👽 执行过程如下：

  1. 解析查询：MySQL解析查询语句。
  2. 执行计划：MySQL生成执行计划，决定如何访问 employees 表。
  3. 逐行扫描：对于 employees 表中的每一行，MySQL检查 department_id 列是否为 1、2 或 3。
  4. 返回结果：匹配的行被返回。

在这个过程中，MySQL对每一行执行简单的比较操作。这种情况下的 IN 语句等价于多个 OR 条件。

SELECT * 
FROM employees 
WHERE department_id = 1 
   OR department_id = 2 
   OR department_id = 3;

1.2.2 子查询的 IN 语句

当 IN 语句包含子查询时，MySQL必须先执行子查询并获取结果集，然后将主查询中的值与子查询结果集中的值进行比较。

📓 示例：

SELECT * 
FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE name = 'WorkDog');

👽 执行过程如下：

  1. 解析查询：MySQL解析主查询和子查询。
  2. 执行子查询：MySQL执行子查询 SELECT id FROM departments WHERE name = 'WorkDog'，生成结果集。
  3. 缓存结果集：将子查询的结果集缓存到内存中。
  4. 执行主查询：MySQL生成主查询的执行计划。
  5. 逐行扫描：对于 employees 表中的每一行，MySQL检查 department_id 列是否在子查询结果集中。
  6. 返回结果：匹配的行被返回。

在这种情况下，子查询的执行方式会影响整体查询的性能。如果子查询结果集较大，MySQL可能会使用临时表来存储结果集，并使用索引来加快查找速度。

🌟 结果集缓存

当使用 IN 子查询时，MySQL会将子查询的结果集缓存到内存中以加快主查询的执行。对于非常大的结果集，这可能会导致内存占用过多。在这种情况下，可以考虑使用临时表或其他优化方法来降低内存使用。

1.3 相关优化

  1. 如果子查询返回的结果集较大，使用 EXISTS 可能会更有效，因为 EXISTS 会在找到匹配的行后立即停止子查询的执行。
  2. 静态值列表：确保在用于比较的列上有适当的索引。例如，上面的 1.2.1 例子 department_id 列上创建索引。
  3. 子查询：确保子查询中使用的列上有适当的索引。例如，上面的 1.2.2 例子中 departments.id 和 departments.name 列上创建索引。
  4. 将 IN 子查询转换为 JOIN 操作，例如：
    -- 使用IN子查询
SELECT * 
FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE name = 'WorkDog');

-- 转换为JOIN
SELECT e.* 
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'WorkDog';

2. EXISTS & NOT EXISTS

EXISTS：EXISTS用于判断是否存在满足子查询条件的结果，常用于复杂的条件检查。子查询可以是一个查询语句，返回一个结果集。

2.1 基本使用

以下是EXISTS关键词的示例用法， NOT EXISTS 同理：

SELECT column_name FROM table_name1 
WHERE EXISTS (
	SELECT column_name FROM table_name2 WHERE condition
);

-- 示例
SELECT * FROM employees e WHERE EXISTS 
	(SELECT 1 FROM departments d WHERE d.name = 'WorkDog' AND e.department_id = d.id);

这将返回table_name1表中满足EXISTS子查询条件的行，子查询是在table_name2表中的一个查询。如果子查询返回结果集，则认为条件满足。

2.2 工作原理

当 MySQL 处理一个包含 EXISTS 子查询的查询时，它会逐条扫描外表的每一行，并对每一行执行一次子查询。如果子查询返回至少一行结果，那么 EXISTS 条件就满足，主查询的那一行就会被包含在最终结果集中，否则只查询的那一行就会被舍弃。

📓 假设有两个表 employees 和 departments，希望找到所有在特定部门（例如 WorkDog）工作的员工：

SELECT * 
FROM employees e 
WHERE EXISTS (
    SELECT 1 
    FROM departments d 
    WHERE e.department_id = d.id 
      AND d.name = 'WorkDog'
);

👽执行过程如下：

  1. 初始化：MySQL 初始化主查询，开始扫描 employees 表。
  2. 逐行扫描外表：对 employees 表中的每一行，执行以下步骤：
    • 读取一行：读取当前行的 department_id。
    • 执行子查询：针对当前行的 department_id 执行子查询
    SELECT 1 
FROM departments d 
WHERE e.department_id = d.id 
  AND d.name = 'WorkDog'
    • 检查子查询结果：如果子查询返回至少一行结果，则 EXISTS 条件满足（true），这一行会被包含在结果集中。如果子查询不返回任何结果，则 EXISTS 条件不满足(false)，这一行会被排除在结果集之外。
  3. 继续扫描：重复步骤2，直到扫描完 employees 表的所有行。
  4. 返回结果：将满足 EXISTS 条件的所有行作为结果返回。

2.3 相关优化

由于 EXISTS 子查询对于外表中的每一行都会执行一次，这意味着子查询的性能对于整个查询的性能至关重要。

  1. 索引使用：确保子查询中的过滤条件上有适当的索引。这可以显著减少子查询的执行时间。

  2. 简化子查询：尽量简化子查询，使其只返回需要的最小数据量。例如，使用 SELECT 1 而不是 SELECT *。

  3. 避免计算：避免在子查询中进行复杂的计算，可以在外部查询中处理这些计算。

3. 两者区别

(1) 用法：

  • IN 关键字可以与常量列表一起使用，也可以与子查询一起使用。适用于在某个字段的值与给定值列表之间进行匹配。它是基于字段值与值列表进行比较的操作符。
  • EXISTS 关键字只能与子查询一起使用。适用于检查是否存在满足子查询条件的结果。它是基于子查询是否返回结果集进行判断的条件。

(2) 功能：

  • IN 关键字用于在一个查询中匹配一个值是否存在于一个列表中。
  • EXISTS 关键字用于检查子查询是否返回任何行。

(3) 子查询结果：

  • IN 关键字的子查询返回的结果集可以是给定的多个值列表，或者是一个单独的查询语句（返回结果必须只有一个字段）。
  • EXISTS 关键字的子查询通常返回一个布尔值，表示子查询是否返回了任何行。

(4) 性能:

  • IN 通常比 EXISTS 更快，尤其是在值列表较小时。因为它不需要执行额外的逻辑来检查是否存在结果。
  • EXISTS 关键字在处理大量数据时比 IN 关键字更高效。这是因为 EXISTS 只需要找到匹配的行，并返回结果，而不需要返回整个列表；性能可能会受到子查询的复杂性和数据量的影响。

(5) 空值处理：

IN和EXISTS对待空值的方式不同。

  • 使用IN时，如果给定的值列表中包含空值，将无法通过等值比较来匹配到空值。
  • 而EXISTS则可以判断子查询中是否存在空值结果。

在选择使用 IN 还是 EXISTS 关键字时，需要根据具体的查询需求和数据情况进行考虑。如果只是简单的匹配值是否在列表中，可以使用 IN。如果需要根据子查询的返回结果来决定外部查询的结果，或者需要处理大量数据，那么使用 EXISTS 可能更为适合。


http://www.niftyadmin.cn/n/5559018.html

相关文章

python自动化之用flask校验接口token(把token作为参数）

python自动化之用flask校验接口token(把token作为参数）

用到的库：flask 实现效果: 写一个接口，需要token正确才能登录 代码： # 导包 from flask import Flask,request,jsonify,json # 创建一个服务 appFlask(__name__) # post请求，路径：/query app.route(/query, met…
阅读更多...
c#中的字符串方法

c#中的字符串方法

Concat() String.Concat(字符串1 字符串n) 字符串拼接 Contains () 字符串1.Contains(字符串2) 字符串1是否包含字符串2返回布尔值 CopyTo() 字符串1.CopyTo(0,空数组,0,5); 从哪开始 复制到哪里 从哪开始存 存储的个数 tartsWith 字符串1.StartsWith("字符串") 以…
阅读更多...
java-数据结构与算法-02-数据结构-04-队列

java-数据结构与算法-02-数据结构-04-队列

文章目录 1. 概述2. 链表实现3. 环形数组实现4. 习题E01. 二叉树层序遍历-Leetcode 102Ex1. 设计队列-Leetcode 622Ex2. 设计队列-Leetcode 622Ex3. 设计队列-Leetcode 622 1. 概述 计算机科学中，queue 是以顺序的方式维护的一组数据集合，在一端添加数据…
阅读更多...
制造运营管理系统（MOM系统），企业实现先进制造的关键一步

制造运营管理系统（MOM系统），企业实现先进制造的关键一步

随着全球制造业的快速发展，企业对于生产效率和成本控制的要求日益增高。在这个背景下，制造运营管理系统（MOM系统）成为了企业提升竞争力的关键工具。盘古信息作为业内领先的智能制造解决方案提供商，其MOM系统更是以其卓…
阅读更多...
华为机试题-单车道汽车通行时间-Java

华为机试题-单车道汽车通行时间-Java

代码在最后面 1 题目描述 M（1 ≤ M ≤ 20）辆车需要在一条不能超车的单行道到达终点，起点到终点的距离为 N（1 ≤ N ≤ 400）。 速度快的车追上前车后，只能以前车的速度继续行驶，求最后一辆车到达…
阅读更多...
深入理解PostgreSql域类型(Domain)，灵活定义数据约束，让表结构设计更加严谨

深入理解PostgreSql域类型(Domain)，灵活定义数据约束，让表结构设计更加严谨

在PostgreSQL中，域（Domain）是一种用户定义的数据类型，它基于系统内已存在的数据类型，并可以附加约束条件。使用域可以增强数据的完整性和一致性，因为它允许开发者对特定列设定更为具体的规则，比…
阅读更多...
linux中 crontab 定时器用法

linux中 crontab 定时器用法

*/10 * * * * python3 /home/code/haha2.py Crontab 当然，以下是一个简短的博客，介绍了 Cron 和 Crontab 的用法： --- # 简介：使用 Cron 和 Crontab 在 Linux 中进行定时任务调度 在 Linux 系统中，Cron 是一个用于…
阅读更多...
MySQL-事务、日志

MySQL-事务、日志

事务 特性 原子性 是指事务开始后，必须成功执行完所有的操作才会结束，否则会回滚到事务刚开始前。 拿转账来说，一个成功的 A向B转账100元的过程 会涉及如下过程： A：从数据库读取A的余额；A的余额-100&am…
阅读更多...
最新文章

Copyright @ 2022~2023