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

目次

• 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

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

1.1 基本使用

• 📓 语法一：

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

• 📓 语法二：

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

1.2 工作原理

在MySQL中，[code]IN[/code] 语句用于查抄某个值是否在指定的列表或子查询结果会合。[code]IN[/code] 语句的工作原理包括处理处罚静态值列表和子查询结果集。在差异的环境下，MySQL会采用差异的计谋来实验 [code]IN[/code] 语句。下面详细表明 [code]IN[/code] 语句的工作原理。

1.2.1 静态值列表的 IN 语句

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

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

👽 实验过程如下：

• 剖析查询：MySQL剖析查询语句。
• 实验计划：MySQL生成实验计划，决定如何访问 [code]employees[/code] 表。
• 逐行扫描：对于 [code]employees[/code] 表中的每一行，MySQL查抄 [code]department_id[/code] 列是否为 1、2 或 3。
• 返回结果：匹配的行被返回。

在这个过程中，MySQL对每一行实验简单的比较操作。这种环境下的 [code]IN[/code] 语句等价于多个 [code]OR[/code] 条件。

1.2.2 子查询的 IN 语句

当 [code]IN[/code] 语句包罗子查询时，MySQL必须先实验子查询并获取结果集，然后将主查询中的值与子查询结果会合的值举行比较。

📓 示例：

👽 实验过程如下：

• 剖析查询：MySQL剖析主查询和子查询。
• 实验子查询：MySQL实验子查询 [code]SELECT id FROM departments WHERE name = 'WorkDog'[/code]，生成结果集。
• 缓存结果集：将子查询的结果集缓存到内存中。
• 实验主查询：MySQL生成主查询的实验计划。
• 逐行扫描：对于 [code]employees[/code] 表中的每一行，MySQL查抄 [code]department_id[/code] 列是否在子查询结果会合。
• 返回结果：匹配的行被返回。

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

🌟 结果集缓存

1.3 相干优化

• 如果子查询返回的结果集较大，使用 [code]EXISTS[/code] 可能会更有效，由于 [code]EXISTS[/code] 会在找到匹配的行后立刻制止子查询的实验。
• 静态值列表：确保在用于比较的列上有得当的索引。比方，上面的 1.2.1 例子 [code]department_id[/code] 列上创建索引。
• 子查询：确保子查询中使用的列上有得当的索引。比方，上面的 1.2.2 例子中 [code]departments.id[/code] 和 [code]departments.name[/code] 列上创建索引。
• 将 [code]IN[/code] 子查询转换为 [code]JOIN[/code] 操作，比方：[code]-- 使用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'; [/code]

2. EXISTS & NOT EXISTS

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

2.1 基本使用

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

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

2.2 工作原理

📓 假设有两个表 [code]employees[/code] 和 [code]departments[/code]，希望找到所有在特定部门（比方 [code]WorkDog[/code]）工作的员工：

👽实验过程如下：

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

2.3 相干优化

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

• 索引使用：确保子查询中的过滤条件上有得当的索引。这可以显著淘汰子查询的实验时间。

• 简化子查询：只管简化子查询，使其只返回必要的最小数据量。比方，使用 [code]SELECT 1[/code] 而不是 [code]SELECT *[/code]。

• 避免盘算：避免在子查询中举行复杂的盘算，可以在外部查询中处理处罚这些盘算。

3. 两者区别

(1) 用法：

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

(2) 功能：

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

(3) 子查询结果：

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

(4) 性能:

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

(5) 空值处理处罚：

[code]IN[/code]和[code]EXISTS[/code]对待空值的方式差异。

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

在选择使用 [code]IN[/code] 照旧 [code]EXISTS[/code] 关键字时，必要根据详细的查询需求和数据环境举行考虑。如果只是简单的匹配值是否在列表中，可以使用 [code]IN[/code]。如果必要根据子查询的返回结果来决定外部查询的结果，大概必要处理处罚大量数据，那么使用 [code]EXISTS[/code] 可能更为得当。

总结

到此这篇关于MySQL关键字IN与EXISTS的使用与区别的文章就先容到这了,更多相干MySQL关键字IN与EXISTS使用内容请搜索脚本之家以前的文章或继续浏览下面的相干文章希望大家以后多多支持脚本之家！