Oracle-SQL-优化：in/exists ， not in/not exists

1 IN 与 EXISTS 对比

特性	IN	EXISTS	关键结论
等价性	10g+ 版本与 EXISTS 等价	10g+ 版本与 IN 等价	优化器会自动做查询转换（Unnest）
性能表现	自动 Unnest 后性能相同	自动 Unnest 后性能相同	主流版本性能无差异
特殊情况	极少数参数屏蔽时可能不触发 Unnest	极少数参数屏蔽时可能不触发 Unnest	建议优先使用 IN（触发 Unnest 概率更高）
OR 连接陷阱	与其他谓词 OR 连接时无法 Unnest（如 col='X' OR IN(...)）	与其他谓词 OR 连接时无法 Unnest（如 col='X' OR EXISTS(...)）	必须避免 OR 连接，改用 UNION ALL 改写
改写方案	SELECT ... FROM t1 WHERE col IN (...) → SELECT ... FROM t1 WHERE col='X' UNION ALL SELECT ... FROM t1 WHERE col IN (...)	同左	消除 OR 连接可恢复高性能

---

2 NOT IN 与 NOT EXISTS 对比

2.1 场景前提

• 测试表结构：

  CREATE TABLE tnull_1 (id1 NUMBER, name1 VARCHAR2(20));
  CREATE TABLE tnull_2 (id2 NUMBER, name2 VARCHAR2(20));

• 测试数据：

  • tnull_1：(1,'a1'), (2,'b1'), (3,'c1'), (null,'d1')

  • tnull_2：(2,'b2_1'), (2,'b2_2'), (3,'c2'), (4,'d2'), (null,'e2')

2.2 结果集与性能对比

查询类型	SQL 示例	结果集	执行计划特征	性能	关键结论
NOT EXISTS	SELECT id1,name1 FROM tnull_1 WHERE NOT EXISTS (SELECT 1 FROM tnull_2 WHERE id1=id2);	包含 NULL 记录 (null,'d1'), (1,'a1')	ANTI JOIN	高	✅ 推荐写法
NOT IN (含 NULL)	SELECT id1,name1 FROM tnull_1 WHERE id1 NOT IN (SELECT id2 FROM tnull_2);	空结果集	ANTI NA	低	❌ 危险！
NOT IN (去 NULL)	SELECT id1,name1 FROM tnull_1 WHERE id1 NOT IN (SELECT id2 FROM tnull_2 WHERE id2 IS NOT NULL);	排除 NULL (1,'a1')	ANTI SNA	中	⚠️ 不完整
NOT IN (完全体)	SELECT id1,name1 FROM tnull_1 WHERE id1 NOT IN (SELECT id2 FROM tnull_2 WHERE id2 IS NOT NULL) AND id1 IS NOT NULL;	排除 NULL (1,'a1')	ANTI JOIN	高	✅ 安全写法

---

3 NULL 值处理规则

场景	NOT IN 行为	NOT EXISTS 行为	建议
子查询含 NULL	整个结果集为空	正常返回其他记录	NOT IN 必须加 IS NOT NULL 过滤
主查询含 NULL	不返回 NULL 记录	返回 NULL 记录	按业务需求决定是否保留 NULL
两表字段均为 NOT NULL	结果集与 NOT EXISTS 相同	结果集与 NOT IN 相同	此时两者等价
任意表字段可为 NULL	结果集可能为空或不包含 NULL 记录	结果集始终包含未匹配记录（含 NULL）	优先使用 NOT EXISTS

---

4 终极使用指南

4.1. IN / EXISTS 选择原则

• ✅ 默认用 IN：更直观且触发 Unnest 的概率更高

• ❌ 禁用 OR 连接：

  -- 错误写法（导致 Filter 低效）
  SELECT ... WHERE col='X' OR EXISTS (SELECT ...);
  
  -- 正确改写
  SELECT ... WHERE col='X'
  UNION ALL
  SELECT ... WHERE col <> 'X' AND EXISTS (SELECT ...);

4.2. NOT IN / NOT EXISTS 选择原则

• ✅ 首选 NOT EXISTS：

  SELECT ... FROM main 
  WHERE NOT EXISTS (SELECT 1 FROM sub WHERE main.key = sub.key);

• ⚠️ 必须用 NOT IN 时：

  SELECT ... FROM main
  WHERE key NOT IN (SELECT key FROM sub WHERE key IS NOT NULL) 
    AND key IS NOT NULL; -- 双重保护

4.3. 字段设计建议

• 🔧 关联字段显式定义 NOT NULL：

  CREATE TABLE tnull_1 (id1 NUMBER NOT NULL, ...); -- 避免 NULL 陷阱

• 📊 业务逻辑审查：

  • 明确是否需要返回 NULL 记录

  • 明确 NULL 值在关联中的含义

---

5 执行计划性能标记

执行计划关键字	含义	性能等级	触发场景
ANTI JOIN	高效反连接	高	NOT EXISTS 或 完美 NOT IN
ANTI SNA	子查询无 NULL 的反连接	中	NOT IN 子查询去 NULL 但主表未过滤
ANTI NA	处理 NULL 的反连接	低	NOT IN 子查询含 NULL
FILTER	逐行过滤（最差）	极低	未触发 Unnest 或 OR 连接导致

---

6 总结

1. IN/EXISTS

   • 主流版本性能相同

   • 绝对避免与其他条件用 OR 连接

   • 建议优先使用 IN

2. NOT IN/NOT EXISTS

   • ✅ 首选 NOT EXISTS：结果集安全、性能最优

   • ⚠️ 慎用 NOT IN：必须双重检查

     NOT IN (SELECT ... WHERE col IS NOT NULL) AND col IS NOT NULL

   • 字段设计为 NOT NULL 可避免 NULL 陷阱

3. 验证工具

   • 使用 EXPLAIN PLAN 检查执行计划是否含 ANTI JOIN

   • 确认执行计划无 FILTER 操作

测试环境：Oracle 11.2.0.3，结论适用于 10g 及以上版本。更高版本（如 19c）优化器行为可能微调，但基本原则不变。