当前位置： 首页 > news >正文

EXISTS 和 NOT EXISTS 、IN (和 NOT IN)

news 2025/6/29 19:36:46

在 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.lqws.cn/news/563221.html

相关文章：

  • 每日算法刷题Day40 6.27:leetcode前缀和3道题，用时1h20min
  • 1.2 基于蜂鸟E203处理器的完整开发流程
  • 【大模型】Query 改写常见Prompt 模板
  • 【转】PostgreSql的镜像地址
  • InfluxDB 3 Core最后值缓存深度实践：毫秒级响应实时数据的核心引擎
  • Mysql架构
  • c++学习（五、函数高级）
  • 大事件项目记录11-文章分类接口开发-删除文章分类
  • Qt：QCustomPlot库简介
  • Vue基础(18)_收集表单数据
  • debian国内安装docker
  • 【经验】bitsandbytes安装-LLAVA-1.5库调试
  • 【数据标注师】分类标注
  • AD 学习笔记——第一章 系统的安装及参数设置
  • 一个简单测试Deepseek吞吐量的脚本,国内环境可跑
  • 印度和澳洲的地理因素
  • 西门子S7-200 SMART PLC：小型自动化领域的高效之选
  • 数据库（MYsql）
  • Qt-Advanced-Docking-System 关闭、禁止拖动、最大化按钮等设置
  • 从静态到动态：Web渲染模式的演进和突破
  • Spring Cloud：高级特性与最佳实践
  • 布林带的使用
  • 华为云Flexus+DeepSeek征文 |华为云ModelArts Studio集成OpenAI Translator：开启桌面级AI翻译新时代
  • Pytest自动化测试执行环境切换的2种解决方案
  • Linux基本命令篇 —— less命令
  • c++学习（四、引用）
  • ClickHouse基础知识
  • 【编译原理】期末
  • 14-C#的弹出的窗口输入与输出
  • 在C++中#pragma“可选预处理指令的作用“。