MySQL关键字IN与EXISTS的使用与区别详解!
MySQL关键字IN与EXISTS的使用与区别详解!
1. IN & NOT IN
IN
:IN
用于判断某个字段的值是否存在于给定的值列表中,常用于简单的列表匹配。可以使用单个值,也可以使用一个由多个值组成的列表,也可以是一个子查询。以下是IN
关键词的示例用法:
1.1 基本使用
- 📓 语法一:
1
2
3
4
5
|
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)
中。
- 📓 语法二:
1
2
3
4
5
6
|
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
的部门:
1
2
3
|
SELECT * FROM employees WHERE department_id IN (1, 2, 3); |
👽 执行过程如下:
- 解析查询:MySQL解析查询语句。
- 执行计划:MySQL生成执行计划,决定如何访问
employees
表。 - 逐行扫描:对于
employees
表中的每一行,MySQL检查department_id
列是否为 1、2 或 3。 - 返回结果:匹配的行被返回。
在这个过程中,MySQL对每一行执行简单的比较操作。这种情况下的 IN
语句等价于多个 OR
条件。
1
2
3
4
5
|
SELECT * FROM employees WHERE department_id = 1 OR department_id = 2 OR department_id = 3; |
1.2.2 子查询的 IN 语句
当 IN
语句包含子查询时,MySQL必须先执行子查询并获取结果集,然后将主查询中的值与子查询结果集中的值进行比较。
📓 示例:
1
2
3
|
SELECT * FROM employees WHERE department_id IN ( SELECT id FROM departments WHERE name = 'WorkDog' ); |
👽 执行过程如下:
- 解析查询:MySQL解析主查询和子查询。
- 执行子查询:MySQL执行子查询
SELECT id FROM departments WHERE name = 'WorkDog'
,生成结果集。 - 缓存结果集:将子查询的结果集缓存到内存中。
- 执行主查询:MySQL生成主查询的执行计划。
- 逐行扫描:对于
employees
表中的每一行,MySQL检查department_id
列是否在子查询结果集中。 - 返回结果:匹配的行被返回。
在这种情况下,子查询的执行方式会影响整体查询的性能。如果子查询结果集较大,MySQL可能会使用临时表来存储结果集,并使用索引来加快查找速度。
🌟 结果集缓存
当使用
IN
子查询时,MySQL会将子查询的结果集缓存到内存中以加快主查询的执行。对于非常大的结果集,这可能会导致内存占用过多。在这种情况下,可以考虑使用临时表或其他优化方法来降低内存使用。
1.3 相关优化
- 如果子查询返回的结果集较大,使用
EXISTS
可能会更有效,因为EXISTS
会在找到匹配的行后立即停止子查询的执行。 - 静态值列表:确保在用于比较的列上有适当的索引。例如,上面的 1.2.1 例子
department_id
列上创建索引。 - 子查询:确保子查询中使用的列上有适当的索引。例如,上面的 1.2.2 例子中
departments.id
和departments.name
列上创建索引。 - 将
IN
子查询转换为JOIN
操作,例如:12345678910-- 使用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
同理:
1
2
3
4
5
6
7
8
|
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
)工作的员工:
1
2
3
4
5
6
7
8
|
SELECT * FROM employees e WHERE EXISTS ( SELECT 1 FROM departments d WHERE e.department_id = d.id AND d. name = 'WorkDog' ); |
👽执行过程如下:
- 初始化:MySQL 初始化主查询,开始扫描 employees 表。
- 逐行扫描外表:对 employees 表中的每一行,执行以下步骤:
- 读取一行:读取当前行的 department_id。
- 执行子查询:针对当前行的 department_id 执行子查询
1234SELECT
1
FROM
departments d
WHERE
e.department_id = d.id
AND
d.
name
=
'WorkDog'
- 检查子查询结果:如果子查询返回至少一行结果,则 EXISTS 条件满足(true),这一行会被包含在结果集中。如果子查询不返回任何结果,则 EXISTS 条件不满足(false),这一行会被排除在结果集之外。
- 继续扫描:重复步骤2,直到扫描完 employees 表的所有行。
- 返回结果:将满足 EXISTS 条件的所有行作为结果返回。
2.3 相关优化
由于 EXISTS
子查询对于外表中的每一行都会执行一次,这意味着子查询的性能对于整个查询的性能至关重要。
- 索引使用:确保子查询中的过滤条件上有适当的索引。这可以显著减少子查询的执行时间。
- 简化子查询:尽量简化子查询,使其只返回需要的最小数据量。例如,使用
SELECT 1
而不是SELECT *
。 - 避免计算:避免在子查询中进行复杂的计算,可以在外部查询中处理这些计算。
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
可能更为适合。
总结
到此这篇关于MySQL关键字IN与EXISTS的使用与区别的文章就介绍到这了。
学习资料见知识星球。
以上就是今天要分享的技巧,你学会了吗?若有什么问题,欢迎在下方留言。
快来试试吧,小琥 my21ke007。获取 1000个免费 Excel模板福利!
更多技巧, www.excelbook.cn
欢迎 加入 零售创新 知识星球,知识星球主要以数据分析、报告分享、数据工具讨论为主;
1、价值上万元的专业的PPT报告模板。
2、专业案例分析和解读笔记。
3、实用的Excel、Word、PPT技巧。
4、VIP讨论群,共享资源。
5、优惠的会员商品。
6、一次付费只需99元,即可下载本站文章涉及的文件和软件。
共有 0 条评论