在网上搜了下关于oracle中not exists和not in性能的比较，发现没有描述的太全面的，可能是问题太简单了，达人们都不屑于解释吧。于是自己花了点时间，试图把这个问题简单描述清楚，其实归根结底一句话：not in性能并不比not exists差，关键看你用的是否正确。
我先建两个示范表，便于说明：
create table ljn_test1 (col number);
create table ljn_test2 (col number);
然后插入一些数据：
insert into ljn_test1
select level from dual connect by level <=30000;
insert into ljn_test2
select level+1 from dual connect by level <=30000;
commit;
然后来分别看一下使用not exists和not in的性能差异：
select * from ljn_test1 where not exists (select 1 from ljn_test2 where ljn_test1.col = ljn_test2.col);
COL
----------
1
Elapsed: 00:00:00.06
select * from ljn_test1 where col not in (select col from ljn_test2);
COL
----------
1
Elapsed: 00:00:21.28
可以看到，使用not exists需要0.06秒，而使用not in需要21秒，差了3个数量级！为什么呢？其实答案很简答，以上两个SQL其实并不是等价的。
我把以上两个表的数据清除掉，重新插入数据：
truncate table ljn_test1;
truncate table ljn_test2;
insert into ljn_test1 values(1);
insert into ljn_test1 values(2);
insert into ljn_test1 values(3);
insert into ljn_test2 values(2);
insert into ljn_test2 values(null);
commit;
然后再次执行两个SQL：
select * from ljn_test1 where not exists (select 1 from ljn_test2 where ljn_test1.col = ljn_test2.col);
COL
----------
3
1
select * from ljn_test1 where col not in (select col from ljn_test2);
no rows selected
这回not in的原形暴露了，竟然得到的是空集。来仔细分解一下原因：
A. select * from ljn_test1 where col not in (select col from ljn_test2);
A在这个例子中可以转化为下面的B：
B. select * from ljn_test1 where col not in (2,null);
B可以进一步转化为下面的C：
C. select * from ljn_test1 where col <> 2 and col <> null;
因为col <> null是一个永假式，所以最终查出的结果肯定也就是空了。
由此可以得出结论：只要not in的子查询中包含空值，那么最终的结果就为空！
not exists语句不会出现这种情况，因为not exists子句中写的是ljn_test1与ljn_test2的关联，null是不参与等值关联的，所以ljn_test2的col存在空值对最终的查询结果没有任何影响。
我在这里暂且把ljn_test1叫做外表，ljn_test2叫做内表。
只要稍做归纳，就可以得到更详细的结论：
1、对于not exists查询，内表存在空值对查询结果没有影响；对于not in查询，内表存在空值将导致最终的查询结果为空。
2、对于not exists查询，外表存在空值，存在空值的那条记录最终会输出；对于not in查询，外表存在空值，存在空值的那条记录最终将被过滤，其他数据不受影响。
讲到这里，我就可以开始解释为什么上面的not in语句比not exists语句效率差这么多了。
not exists语句很显然就是一个简单的两表关联，内表与外表中存在空值本身就不参与关联，在CBO(基于成本的优化器)中常用的执行计划是hash join，所以它的效率完全没有问题，看一下它的执行计划：
set autot on;
select * from ljn_test1 where not exists (select 1 from ljn_test2 where ljn_test1.col = ljn_test2.col);
COL
----------
3
1
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 385135874
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 78 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 3 | 78 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| LJN_TEST1 | 3 | 39 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| LJN_TEST2 | 2 | 26 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("LJN_TEST1"."COL"="LJN_TEST2"."COL")
这个执行计划很清晰，没有什么需要解释的，再看一下not in:
select * from ljn_test1 where col not in (select col from ljn_test2);
no rows selected
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3267714838
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| LJN_TEST1 | 3 | 39 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| LJN_TEST2 | 2 | 26 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "LJN_TEST2" "LJN_TEST2"
WHERE LNNVL("COL"<>:B1)))
3 - filter(LNNVL("COL"<>:B1))
可以看到关联谓词是filter，它类似于两表关联中的nested loop，也就是跑两层循环，可见它的效率有多差。为什么not in不能使用hash join作为执行计划呢？正如上面解释的，因为内表或外表中存在空值对最终结果产生的影响是hash join无法实现的，因为hash join不支持把空值放到hash桶中，所以它没办法处理外表和内表中存在的空值，效率与正确性放在一起时，肯定是要选择正确性，所以oracle必须放弃效率，保证正确性，采用filter谓词。
这个执行计划中我们还有感兴趣的东西，那就是：LNNVL("COL"<>:B1)，关于LNNVL的解释可以参见官方文档：http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions078.htm
它在这里的作用很巧妙，oracle知道使用filter性能很差，所以它在扫描内表ljn_test2时，会使用LNNVL来检查ljn_test2.col是否存在null值，只要扫描到null值，就可以断定最终的结果为空值，也就没有了继续执行的意义，所以oracle可以马上终止执行，在某种意义上它弥补了filter较差的性能。
我用例子来证明这一点，首先先造一些数据：
truncate table ljn_test1;
truncate table ljn_test2;
insert into ljn_test1
select level from dual connect by level <=30000;
insert into ljn_test2
select level+1 from dual connect by level <=30000;
commit;
然后我为了让oracle尽快扫描到ljn_test2.col为null的那条记录，我要先找到物理地址最小的那条记录，因为通常情况全表扫描会先扫描物理地址最小的那条记录：
select col from ljn_test2 where rowid=(select min(rowid) from ljn_test2);
COL
----------
1982
然后我把这条记录更新为空：
update ljn_test2 set col = null where col=1982;
commit;
然后再来看一下not in的查询效率：
select * from ljn_test1 where col not in (select col from ljn_test2);
no rows selected
Elapsed: 00:00:00.17
看到这个结果后我很爽，它和之前查询需要用时21秒有很大的差别！
当然，我们不能总是指望oracle扫描表时总是最先找到null值，看下面的例子：
update ljn_test2 set col = 1982 where col is null;
select col from ljn_test2 where rowid=(select max(rowid) from ljn_test2);
COL
----------
30001
update ljn_test2 set col = null where col=30001;
commit;
再看一下not in的查询效率：
select * from ljn_test1 where col not in (select col from ljn_test2);
COL
----------
1
Elapsed: 00:00:21.11
这一下not in再一次原形毕露了！
机会主义不行，更杯具的是如果内表中没有空值，那LNNVL优化就永远起不到作用，相反它还会增大开销！
其实只要找到原因，问题很好解决，不就是空值在作怪嘛！在正常的逻辑下用户本来就是想得到和not exists等价的查询结果，所以只要让oracle知道我们不需要空值参与进来就可以了。
第一种解决方案：
将内表与外表的关联字段设定为非空的：
alter table ljn_test1 modify col not null;
alter table ljn_test2 modify col not null;
好了，再看一下执行计划：
set autot on;
select * from ljn_test1 where col not in (select col from ljn_test2);
COL
----------
1
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value: 385135874
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 28 (8)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 1 | 26 | 28 (8)| 00:00:01 |
| 2 | TABLE ACCESS FULL| LJN_TEST1 | 30000 | 380K| 13 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| LJN_TEST2 | 30000 | 380K| 13 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("COL"="COL")
很好！这回oracle已经知道使用hash join了！不过有时候表中需要存储空值，这时候就不能在表结构上指定非空了，那也同样简单：
第二种解决方案：
查询时在内表与外表中过滤空值。
先把表结构恢复为允许空值的：
alter table ljn_test1 modify col null;
alter table ljn_test2 modify col null;
然后改造查询：
select * from ljn_test1 where col is not null and col not in (select col from ljn_test2 where col is not null);
COL
----------
1
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value: 385135874
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 28 (8)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 1 | 26 | 28 (8)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| LJN_TEST1 | 30000 | 380K| 13 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| LJN_TEST2 | 30000 | 380K| 13 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("COL"="COL")
2 - filter("COL" IS NOT NULL)
3 - filter("COL" IS NOT NULL)
OK! hash join出来了！我想我关于not exists与not in之间的比较也该结束了。
分享到：
相关推荐
`EXISTS`和`IN`的区别在于，`IN`通常用于比较单个字段值，而`EXISTS`可以处理更复杂的查询，它关心的是子查询是否返回行，而不关心返回的具体值。`IN`通常用于获取特定值列表内的记录，而`EXISTS`则可以用于检查记录...
这是因为`NOT EXISTS`通常能更好地优化，特别是当它与索引配合使用时，而`NOT IN`在处理空值时可能产生意外的结果。 例如，考虑以下`NOT IN`的用法： ```sql SELECT a.* FROM tableA a WHERE a.column1 NOT IN ...
NOT EXISTS 与 NOT IN 的区别 NOT EXISTS 和 NOT IN 都可以用于判断子查询的结果，但是它们的使用场景不同。 NOT EXISTS 子句判断子查询是否不返回记录，而 NOT IN 子句判断子查询的结果是否不在某个集合中。 ...
3. **与类方法的区别**：需要注意的是，`function_exists()` 只能用于检查全局函数的存在性，而不能用于检查类的方法。对于类的方法，可以使用 `method_exists()` 函数来进行检查。 4. **与其他语言的差异**：不同...
然而，它对查询性能的影响可能并不总是直观的，尤其是在涉及到外层查询时。本文将深入探讨`NOT EXISTS`如何影响外层查询，并通过示例解释在不同版本的Oracle数据库中可能存在的差异。 首先，让我们回顾`NOT EXISTS`...
此外，避免使用`NOT IN`而选择`NOT EXISTS`或`LEFT JOIN`等替代方案，因为在某些情况下，`NOT IN`可能引发全表扫描，而`NOT EXISTS`则能更有效地利用索引。 #### `IN`与`EXISTS`的选择 在进行多表关联查询时，`IN`...
### 写出高性能SQL语句的35条方法详解 #### 1. 使用索引优化查询性能 - **描述**：对于频繁访问的列或条件，合理创建索引可以极大提高查询速度。 - **实现**：例如，在进行关联查询时，确保连接列有索引；在`WHERE...
- **`UNION` 与 `UNION ALL` 的区别**：`UNION` 用于合并两个查询结果，并自动去除重复的行；而 `UNION ALL` 也合并结果但不删除重复行。通常情况下，如果不需要去重，使用 `UNION ALL` 会更快，因为它不需要额外的...
NOT IN与EXISTS** 使用`NOT IN`时，可能会导致全表扫描，尤其是在比较大的数据集上。相比之下，使用`NOT EXISTS`可以更早终止查询，提高效率。 #### 二、SQL语句性能优化策略 **1. 选用合适的ORACLE优化器** ...
操作符通常会导致全表扫描，这是因为Oracle的索引结构（如B树）并不支持非相等条件下的快速定位。即使在索引列上执行查询，也无法利用索引加速。 **推荐方案**：通过重写查询条件，使用大于或小于等操作符代替，...
NOT EXISTS也比NOT IN更高效，原因与EXISTS类似。 #### 18. EXISTS与DISTINCT 在某些情况下，使用EXISTS代替DISTINCT可以提高性能。 #### 19. SQL语句的编写技巧 编写高效的SQL语句需要注意多方面的细节，如避免...
5. **全表扫描**：避免使用可能导致全表扫描的查询条件，例如 `COL1 > [=] COL2`（相同表）、`COL1 IS [NOT] NULL`、`COL1 NOT IN`、`COL1 LIKE '%PATTERN%'`、`NOT EXISTS` 等。 6. **索引列运算**：对索引列进行...
- **创建(CREATE)**: 使用SQL语句`CREATE TABLE`创建新表，例如：`CREATE TABLE IF NOT EXISTS TableName (Column1 DataType, Column2 DataType, ...);` - **读取(READ)**: `SELECT`语句用于查询数据，如：`SELECT *...
- **NOT IN**：使用NOT IN可能产生大量的临时表，降低性能。推荐使用LEFT JOIN加上IS NULL检查来替代。 - **NOT BETWEEN**：相比于使用范围查询，如`WHERE a > x AND a ，NOT BETWEEN可能导致索引失效。 - **LIKE...
- **解决方案**: 使用EXISTS或NOT EXISTS代替IN来提高查询性能。 #### 三、SQL语句性能优化 ##### 3.1 选用合适的Oracle优化器 - **选择**: Oracle提供了两种优化器——基于规则的优化器(RBO)和基于成本的优化器...
### SQL Server 2005 性能优化详解 #### 一、引言 SQL Server 2005是一款功能强大的数据库管理系统，在企业级应用中非常常见。为了提高查询效率和响应速度，对SQL Server 2005进行性能优化至关重要。本文将详细探讨...
2. **使用 EXISTS 而不是 IN 或者 NOT IN**：EXISTS 和 NOT EXISTS 通常比 IN 和 NOT IN 效率更高。 - **示例**：将 `SELECT * FROM EMP WHERE EMPNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB')` 替换为 `...
- 避免使用`IN`、`NOT IN`等，转而使用`EXISTS`或`NOT EXISTS`。 - 例如，`WHERE id IN (SELECT id FROM other_table)`可以优化为`WHERE EXISTS (SELECT 1 FROM other_table WHERE other_table.id = main_table.id...