Oracle中NOT IN和NOT EXISTS的NULL值陷阱，90%程序员都踩过这个雷，附测试过程！

在Oracle中，NOT IN和NOT EXISTS在功能上类似，但不完全等价，主要区别在于对NULL值的处理。

主要区别

1. NULL值处理

• NOT IN：当子查询返回NULL值时，整个查询不会返回任何结果
• NOT EXISTS：不受子查询中NULL值的影响

2. 性能差异

• NOT EXISTS：通常性能更好，特别是当子查询表有索引时
• NOT IN：可能需要进行全表扫描

测试用例

创建测试表和数据

-- 创建员工表
CREATE TABLE employees (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    dept_id NUMBER
);

-- 创建部门表
CREATE TABLE departments (
    dept_id NUMBER,
    dept_name VARCHAR2(50)
);

-- 插入测试数据
INSERT INTO employees VALUES (1, '张三', 10);
INSERT INTO employees VALUES (2, '李四', 20);
INSERT INTO employees VALUES (3, '王五', 30);
INSERT INTO employees VALUES (4, '赵六', NULL);

INSERT INTO departments VALUES (10, '技术部');
INSERT INTO departments VALUES (20, '销售部');
INSERT INTO departments VALUES (NULL, '未知部门');

COMMIT;

测试1：不含NULL值的情况

-- 使用NOT IN
SELECT emp_name 
FROM employees 
WHERE dept_id NOT IN (SELECT dept_id FROM departments WHERE dept_id IS NOT NULL);
EMP_NAME
-------------
王五

-- 使用NOT EXISTS
SELECT emp_name 
FROM employees e 
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.dept_id = e.dept_id and d.dept_Id is not null);

EMP_NAME
----------
王五
赵六

测试2：子查询包含NULL值的情况

-- 使用NOT IN（包含NULL）
SELECT emp_name 
FROM employees 
WHERE dept_id NOT IN (SELECT dept_id FROM departments);
no rows selected

-- 使用NOT EXISTS（包含NULL）
SELECT emp_name 
FROM employees e 
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.dept_id = e.dept_id);
EMP_NAME
------------
王五
赵六

结果：

• NOT IN：无结果返回
• NOT EXISTS：返回员工"王五"和"赵六"

测试3：性能对比

-- 创建索引
CREATE INDEX idx_emp_dept ON employees(dept_id);
CREATE INDEX idx_dept_id ON departments(dept_id);

-- 执行计划查看
set lin 200
EXPLAIN PLAN FOR 
SELECT emp_name FROM employees 
WHERE dept_id NOT IN (SELECT dept_id FROM departments WHERE dept_id IS NOT NULL);

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 执行计划
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2688340956

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     4 |   212 |     3   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI SNA|             |     4 |   212 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES   |     4 |   160 |     2   (0)| 00:00:01 |
|*  3 |   INDEX FULL SCAN  | IDX_DEPT_ID |     2 |    26 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPT_ID"="DEPT_ID")
   3 - filter("DEPT_ID" IS NOT NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

20 rows selected.
EXPLAIN PLAN FOR 
SELECT emp_name FROM employees e 
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.dept_id = e.dept_id);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 执行计划：
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2184471969

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     4 |   212 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |             |     4 |   212 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES   |     4 |   160 |     2   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | IDX_DEPT_ID |     1 |    13 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("D"."DEPT_ID"="E"."DEPT_ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

19 rows selected.

总结

最佳实践建议

1. 优先使用NOT EXISTS，因为它更安全且通常性能更好
2. 如果使用NOT IN，确保子查询列有NOT NULL约束或使用WHERE过滤NULL
3. 对于大数据量查询，往往NOT EXISTS配合索引效果最佳