oracle：NOT IN

在Oracle数据库中，NOT IN运算符的使用需要特别注意其与NULL值的交互问题及性能优化方案。以下是关键要点：
1. ‌NOT IN与NULL值的陷阱‌

当子查询结果集中包含NULL值时，NOT IN会导致查询返回空集。这是因为NOT IN的逻辑等价于多个AND连接的<>条件，而任何值与NULL比较的结果均为UNKNOWN，最终使整个条件失效。
示例‌：
-- 若子查询返回包含NULL的结果集，以下查询将无输出
SELECT * FROM table1 WHERE col1 NOT IN (SELECT col2 FROM table2);

2. ‌替代方案与优化建议‌
（1）使用NOT EXISTS

通过关联主表与子查询避免NULL值问题，且通常效率更高：
SELECT * FROM table1 t1
WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t2.col2 = t1.col1);

（2）使用LEFT JOIN

通过外连接筛选未匹配的记录：
SELECT t1.* FROM table1 t1
LEFT JOIN table2 t2 ON t1.col1 = t2.col2
WHERE t2.col2 IS NULL;

（3）显式处理NULL值

若必须使用NOT IN，需排除子查询中的NULL值：
SELECT * FROM table1
WHERE col1 NOT IN (SELECT col2 FROM table2 WHERE col2 IS NOT NULL);

3. ‌性能对比与适用场景‌

    ‌NOT IN‌：适合子查询结果集小且无NULL值的情况，但需注意全表遍历的性能开销。
    ‌NOT EXISTS‌：推荐用于大表关联，可利用索引提升效率。
    ‌LEFT JOIN‌：适用于需要同时输出关联表字段的场景。

4. ‌实际案例中的问题解决‌

某案例中，使用NOT IN过滤状态为“已作废”的记录时，因字段存在NULL值导致数据遗漏。修正方案为改用NOT EXISTS或添加OR IS NULL条件：
-- 修正方案1
SELECT * FROM table WHERE status NOT IN ('已作废') OR status IS NULL;
-- 修正方案2（推荐）
SELECT * FROM table t
WHERE NOT EXISTS (SELECT 1 FROM table WHERE id = t.id AND status = '已作废');

总结

在Oracle中应优先使用NOT EXISTS或LEFT JOIN替代NOT IN，尤其在涉及NULL值或大数据量时。若必须使用NOT IN，需显式处理NULL值以避免逻辑错误。