EXISTS 和 NOT EXISTS 、IN (和 NOT IN)

在 SQL 中，EXISTS、NOT EXISTS 和 IN 都是用于子查询的条件运算符，用于根据子查询的结果过滤主查询的行。它们之间的区别主要体现在工作方式、效率、对 NULL 值的处理以及适用场景上。

1. EXISTS 和 NOT EXISTS

  • 作用：
    • EXISTS: 检查子查询是否至少返回一行。如果子查询返回至少一行，则条件为 TRUE。
    • NOT EXISTS: 检查子查询是否没有返回任何行。如果子查询返回零行，则条件为 TRUE。
  • 工作方式 (关联子查询)：
    • EXISTS/NOT EXISTS 通常与关联子查询一起使用。
    • 对于主查询的每一行，数据库引擎都会执行一次子查询。
    • 子查询的 WHERE 子句通常会引用主查询当前行的列值（这就是“关联”的含义）。
    • 数据库引擎一旦在子查询中找到一行匹配（对于 EXISTS) 或确认没有匹配（对于 NOT EXISTS)，就会停止执行该次子查询，因为它只需要知道是否存在记录，不需要知道具体有多少条或是什么内容。
  • 效率：
    • 当子查询可能返回大量结果，但你只关心“是否存在”时，EXISTS/NOT EXISTS 通常效率更高。
    • 原因在于它们利用了短路求值：找到第一个匹配项（EXISTS) 或确认没有匹配项（NOT EXISTS) 后立即停止扫描子查询的表。
    • 关联条件（主表列 = 子查询表列）通常能有效利用索引。
  • 对 NULL 的处理：
    • EXISTS/NOT EXISTS 只关心子查询是否返回行。
    • 子查询中的 NULL 值不影响判断。只要子查询能返回至少一行（即使该行所有列都是 NULL)，EXISTS 就为 TRUE；只要子查询返回零行，NOT EXISTS 就为 TRUE。
  • 语法：
    SELECT column1, column2, ...
FROM table_name_main main
WHERE EXISTS (SELECT 1 -- 常用 SELECT 1, 实际返回什么值不重要，重要的是是否有行FROM table_name_sub subWHERE sub.correlated_column = main.correlated_column -- 关联条件AND ... -- 其他子查询条件
);SELECT column1, column2, ...
FROM table_name_main main
WHERE NOT EXISTS (SELECT 1FROM table_name_sub subWHERE sub.correlated_column = main.correlated_column -- 关联条件AND ... -- 其他子查询条件
);
  • 适用场景：
    • 检查主表记录在相关表中是否有对应记录（存在性检查）。
    • 检查主表记录在相关表中是否没有对应记录（缺失性检查）。
    • 当子查询逻辑依赖于主查询的当前行时（必须使用关联子查询）。

示例：找出有订单的客户 (EXISTS)

SELECT CustomerID, CustomerName
FROM Customers c
WHERE EXISTS (SELECT 1FROM Orders oWHERE o.CustomerID = c.CustomerID -- 关联条件：订单的客户ID = 当前客户ID
);
  • 对 Customers 表的每一行，检查 Orders 表中是否有 CustomerID 匹配的订单。只要有一条订单，该客户就会被选出。

示例：找出没有订单的客户 (NOT EXISTS)

SELECT CustomerID, CustomerName
FROM Customers c
WHERE NOT EXISTS (SELECT 1FROM Orders oWHERE o.CustomerID = c.CustomerID -- 关联条件
);
  • 对 Customers 表的每一行，检查 Orders 表中是否有 CustomerID 匹配的订单。如果一条都没有，该客户就会被选出。

2. IN (和 NOT IN)

  • 作用：
    • IN: 检查主查询列的值是否等于子查询返回结果集中的任何一个值。
    • NOT IN: 检查主查询列的值是否不等于子查询返回结果集中的所有值。
  • 工作方式 (非关联子查询 - 通常)：
    • IN/NOT IN 通常与非关联子查询一起使用（但也可以是关联的，效率可能变差）。
    • 数据库引擎会先完整执行一次子查询，生成一个包含所有结果的中间结果集（值列表）。
    • 然后，主查询会检查每行的指定列值是否存在于 (IN) 或不存在于 (NOT IN) 这个预先计算好的中间结果集中。
    • 这个过程类似于 WHERE column = value1 OR column = value2 OR ... (IN) 或 WHERE column <> value1 AND column <> value2 AND ... (NOT IN)。
  • 效率：
    • 当子查询返回的结果集非常小时，IN 可能比较高效，尤其是主查询列有索引且列表值不多时。
    • 当子查询返回的结果集非常大时，IN 可能效率较低：
      • 需要存储整个中间结果集（可能消耗内存/临时空间）。
      • 主查询需要对这个庞大的列表进行查找（哈希或排序查找可能比索引查找慢）。
    • 如果 IN 子查询是关联的，效率通常不如 EXISTS，因为它需要为每一行重新生成或查找那个中间结果集。
  • 对 NULL 的处理 (重要陷阱！)：
    • IN: 如果子查询返回的结果集中包含 NULL，这本身不影响 IN 的判断。value IN (1, 2, NULL) 等价于 value = 1 OR value = 2 OR value = NULL。value = NULL 的结果是 UNKNOWN，但只要有 value=1 或 value=2 为 TRUE，整个条件就是 TRUE。如果 value 既不是 1 也不是 2，结果是 UNKNOWN（按 FALSE 处理）。
    • NOT IN: 对 NULL 值非常敏感！ value NOT IN (1, 2, NULL) 等价于 value <> 1 AND value <> 2 AND value <> NULL。value <> NULL 的结果总是 UNKNOWN。根据逻辑运算规则，TRUE AND UNKNOWN = UNKNOWN，FALSE AND UNKNOWN = FALSE，UNKNOWN AND UNKNOWN = UNKNOWN。所以，只要子查询结果集中包含 NULL，整个 NOT IN 条件对于主查询的任何行都会计算为 UNKNOWN（被当作 FALSE 处理），导致没有行被返回！这是 NOT IN 的最大陷阱。
  • 语法：
    SELECT column1, column2, ...
FROM table_name_main
WHERE column_name_main [NOT] IN (SELECT single_column_name -- 子查询必须只返回一列FROM table_name_sub[WHERE ...] -- 子查询条件
);
  • 适用场景：
    • 检查主查询列的值是否在一个明确的、较小的静态值列表中（如 WHERE Country IN ('USA', 'UK', 'Canada')）。
    • 检查主查询列的值是否在一个独立的、返回少量唯一值的子查询结果集中。
    • 当子查询逻辑不依赖于主查询的当前行时（非关联子查询）。

示例：找出在特定国家的客户 (IN)

SELECT CustomerID, CustomerName
FROM Customers
WHERE Country IN ('Germany', 'France', 'Spain'); -- 静态值列表

SELECT CustomerID, CustomerName
FROM Customers
WHERE Country IN (SELECT DISTINCT SupplierCountry -- 独立子查询，返回少量国家FROM SuppliersWHERE SupplierName LIKE '%Gourmet%'
);

示例：NOT IN 的 NULL 陷阱演示
假设 SubTable 有一列 some_col，其中包含一行 NULL。

SELECT *
FROM MainTable
WHERE main_col NOT IN (SELECT some_col FROM SubTable);
  • 如果 SubTable 的 some_col 包含 NULL，那么无论 main_col 的值是什么，这个查询永远不会返回任何行。因为 main_col NOT IN (..., NULL) 总是计算为 UNKNOWN (FALSE)。

关键区别总结

特性EXISTS / NOT EXISTSIN / NOT IN
核心目的检查存在性 (是否有/没有匹配行)检查成员资格 (值是否在/不在列表中)
工作机制关联子查询为主。对主表每一行执行子查询，找到/找不到即停。非关联子查询为主。先执行子查询生成完整值列表，主查询在列表中查找。
效率倾向通常更高效 (尤其子查询大时)，利用短路和关联索引。小列表高效，大列表可能低效 (需存储和查找大列表)。关联子查询效率差。
处理 NULL安全。只关心行是否存在，NULL 行不影响判断。IN 安全，NULL 在子查询结果中不影响。 NOT IN 危险！子查询结果含 NULL 会导致无结果 (整个条件变 UNKNOWN)。
子查询列子查询 SELECT 列表内容无关紧要 (常用 SELECT 1)。子查询必须且只能返回一列。
主要用途基于相关表的存在性/缺失性检查。与静态值列表或独立小结果集进行值比较。

选择建议

  1. 进行存在性/缺失性检查时 (如“有订单的客户”、“没订单的产品”):
    • 首选 EXISTS (存在) 或 NOT EXISTS (缺失)。效率通常更高，语义更清晰，且完全避免 NOT IN 的 NULL 陷阱。
  2. 与小的、静态的值列表比较时 (如 Country IN ('US', 'UK')):
    • 使用 IN 非常合适且直观。
  3. 与一个独立的、返回少量唯一值的子查询结果比较时:
    • 可以使用 IN。
    • 如果子查询可能返回 NULL 并且你需要使用 NOT IN，务必确保子查询结果集排除 NULL (例如 WHERE NOT IN (SELECT col FROM ... WHERE col IS NOT NULL))，或者直接改用 NOT EXISTS 更安全。
  4. 避免 NOT IN 用于子查询:
    • 强烈建议不要使用 NOT IN (SELECT ...)，尤其是当子查询结果集来源表可能包含 NULL 值时。总是优先用 NOT EXISTS 替代 NOT IN 用于子查询场景。
  5. 关联子查询中的存在性检查:
    • 必须使用 EXISTS/NOT EXISTS。IN 虽然语法上可以写成关联的 (如 WHERE col IN (SELECT ... WHERE correlated_condition))，但其执行计划通常不如 EXISTS 高效。

总结一句话： 做存在性检查就用 EXISTS/NOT EXISTS；和小列表或独立小结果集比较值就用 IN；坚决避免用 NOT IN 检查子查询的结果，用 NOT EXISTS 代替。理解它们的工作机制和 NULL 陷阱对于写出正确高效的 SQL 至关重要。

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

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

医疗标准集中标准化存储与人工智能智能更新协同路径研究（上）

医疗标准集中标准化存储与人工智能智能更新协同路径研究（上）

摘要 为了提高医疗系统中文件管理的效率与质量，本文围绕医疗文档的集中化标准化存储与人工智能驱动的智能更新，构建了一种协同策略研究框架。通过分析医疗文档管理的痛点，结合集中化存储与AI技术的协同路径，提出了一种基于标准化文档处理与智能更新的协同优化方案。研究发现…
阅读更多...
c# 比较两个list 之间元素差异

c# 比较两个list 之间元素差异

在C#中&#xff0c;比较两个List之间元素的差异通常有多种方法&#xff0c;具体取决于你想如何表达这些差异&#xff08;例如&#xff0c;找出存在于一个列表中但不在另一个列表中的元素&#xff09;。下面是一些常用的方法&#xff1a; 1. 使用Except方法 Except方法可以找出…
阅读更多...
使用 KernelSU + PlayIntegrityFix 解决Root后ChatGPT不能使用的问题

使用 KernelSU + PlayIntegrityFix 解决Root后ChatGPT不能使用的问题

参考文章: [GUIDE] &#x1f6e1;️ How to Pass Strong Integrity on Android (Step-by-Step Guide) 刚从iPhone转到Android的用户&#xff0c;买了一加13T&#xff0c;享受刷机折腾的乐趣&#xff0c;结果安装了ChatGPT以后&#xff0c;发现无法使用&#xff0c;报错&#xf…
阅读更多...
STM32安全固件升级：使用自定义 bootloader 实现SD卡固件升级，包含固件加密

STM32安全固件升级：使用自定义 bootloader 实现SD卡固件升级，包含固件加密

前言 在 STM32 嵌入式开发中&#xff0c;Bootloader 是一个不可或缺的模块。ST 公司为 STM32 提供了功能完备的官方 Bootloader&#xff0c;支持多种通信接口&#xff08;如 USART、USB DFU、I2C、SPI 等&#xff09;&#xff0c;适用于标准的固件更新方案。 然而&#xff0c…
阅读更多...
变现与自我提升：加法与乘法的智慧抉择

变现与自我提升：加法与乘法的智慧抉择

在当今这个快速发展的时代&#xff0c;无论是追求财富的变现&#xff0c;还是致力于个人能力的提升&#xff0c;我们都会面临一个关键问题&#xff1a;是分类分步地逐步实现&#xff0c;还是将多种要素混合在一起&#xff1f;是简单地做加法&#xff0c;还是复杂的乘法运算&…
阅读更多...
Outlook总是提示登录微软，怎么办？

Outlook总是提示登录微软，怎么办？

1.问题描述 我的Outlook2021邮箱有一个问题&#xff0c;打开邮箱之后&#xff0c;总是提示让登录Microsoft的账号&#xff08;如图所示&#xff09;&#xff0c;因为个人和公司都没有连接微软&#xff0c;只能关闭&#xff0c;但点击关闭之后&#xff0c;就提示必须需要键入ex…
阅读更多...
探秘 VR 逃生救援技术的奇妙世界​

探秘 VR 逃生救援技术的奇妙世界​

VR 逃生救援技术之所以能为我们带来如此震撼和逼真的体验&#xff0c;背后离不开一系列先进技术的支撑。在 VR 逃生救援体验中&#xff0c;其核心在于利用虚拟现实技术&#xff0c;构建出高度逼真的火灾场景&#xff0c;让参与者仿佛身临其境。​ 在构建火灾场景方面&#xff0…
阅读更多...
nt!MiFlushSectionInternal函数分析从nt!IoSynchronousPageWrite函数到Ntfs!NtfsFsdWrite函数

nt!MiFlushSectionInternal函数分析从nt!IoSynchronousPageWrite函数到Ntfs!NtfsFsdWrite函数

第一部分&#xff1a; while (TRUE) { KeClearEvent (&IoEvent); Status IoSynchronousPageWrite (FilePointer, Mdl, (PLARGE_INTEGER)&StartingOffset…
阅读更多...
linux网络编程socket套接字

linux网络编程socket套接字

套接字概念 Socket本身有“插座”的意思&#xff0c;在Linux环境下&#xff0c;用于表示进程间网络通信的特殊文件类型。本质为内核借助缓冲区形成的伪文件。 既然是文件&#xff0c;那么理所当然的&#xff0c;我们可以使用文件描述符引用套接字。与管道类似的&#xff0c;L…
阅读更多...
【51单片机5毫秒定时器】2022-6-1

【51单片机5毫秒定时器】2022-6-1

缘由单片机的代码&#xff0c;求大家来帮帮我-编程语言-CSDN问答 #include "REG52.h" unsigned char code smgduan[]{0x3f,0x06,0x5b,0x4f,0x66,0x6d,0x7d,0x07,0x7f,0x6f,0x77,0x7c,0x39,0x5e,0x79,0x71,0,64}; //共阴0~F消隐减号 unsigned char Js0, miao0;//中断…
阅读更多...
60% 重构项目陷 “越改越烂” 泥潭！

60% 重构项目陷 “越改越烂” 泥潭！

在软件开发的演进历程中&#xff0c;旧项目重构始终是横亘在开发者面前的一道难题。传统的重构模式主要依靠人工逐行剖析代码&#xff0c;这一过程不仅耗费大量时间与人力成本&#xff0c;而且极易因人为疏漏引发新的问题。数据显示&#xff0c;超过 60% 的重构项目遭遇进度滞后…
阅读更多...
UniApp 开发第一个项目

UniApp 开发第一个项目

UniApp 开发第一个项目全流程指南，涵盖环境搭建、项目创建、核心开发到调试发布，结合最新实践整理而成，适合零基础快速上手： 🧰 一、环境准备（5分钟） 安装开发工具 HBuilderX（官方推荐IDE）：下载 App 开发版，安装路径避免中文或空格 微信开发者工具（调试小程序必备…
阅读更多...
最新文章

Copyright @ 2022~2023