测试准备：
create table t1(col1 number,col2 varchar2(1));
create table t2(col2 varchar2(1),col3 varchar2(2));
insert into t1 values(1,'A');
insert into t1 values(2,'B');
insert into t1 values(3,'C');
insert into t2 values('A','A2');
insert into t2 values('B','B2');
insert into t2 values('D','D2');
commit; SQL> set autot on
SQL> select * from t1 where col2 not in (select col2 from t2); COL1 C
---------- -
3 C Execution Plan
----------------------------------------------------------
Plan hash value: 1275484728 ---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 51 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 3 | 51 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 45 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 3 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL> select * from t1 where col2 <> all (select col2 from t2); COL1 C
---------- -
3 C Execution Plan
----------------------------------------------------------
Plan hash value: 1275484728 ---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 51 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 3 | 51 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 45 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 3 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL> select * from t1 where not exists (select 1 from t2 where col2=t1.col2); COL1 C
---------- -
3 C Execution Plan
----------------------------------------------------------
Plan hash value: 2706079091 ---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 51 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 3 | 51 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 45 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 3 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL>
如果T1表中col2有null值：
insert into t1(col1) values('4');
commit;
SQL> select * from t1 where col2 not in (select col2 from t2);
COL1 C
---------- -
3 C
Execution Plan
----------------------------------------------------------
Plan hash value: 1275484728
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 68 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 4 | 68 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 4 | 60 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 3 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL> select * from t1 where col2 <> all (select col2 from t2);
COL1 C
---------- -
3 C
Execution Plan
----------------------------------------------------------
Plan hash value: 1275484728
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 68 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 4 | 68 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 4 | 60 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 3 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL> select * from t1 where not exists (select 1 from t2 where col2=t1.col2);
COL1 C
---------- -
4
3 C
Execution Plan
----------------------------------------------------------
Plan hash value: 2706079091
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 68 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 4 | 68 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 4 | 60 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 3 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL>
如果T2中col2有null值：
delete from t1 where col1=4;
insert into t2(col3) values('E2');
commit; SQL> select * from t1 where col2 not in (select col2 from t2); no rows selected Execution Plan
----------------------------------------------------------
Plan hash value: 1275484728 ---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 51 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 3 | 51 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 45 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 4 | 8 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL> select * from t1 where col2 <> all (select col2 from t2); no rows selected Execution Plan
----------------------------------------------------------
Plan hash value: 1275484728 ---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 51 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 3 | 51 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 45 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 4 | 8 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL> select * from t1 where not exists (select 1 from t2 where col2=t1.col2); COL1 C
---------- -
3 C Execution Plan
----------------------------------------------------------
Plan hash value: 2706079091 ---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 51 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 3 | 51 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 45 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 4 | 8 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL>
not in、<> all对null值敏感，即not in、、<> all后面的子查询或者常亮集合一旦有null值出现，整个sql的执行结果就为null。
not exists对null值不敏感，即null值对执行结果不会有什么影响。