[20121028]not in与NULL问题.txt
在sql语句中使用not in，在遇到子表含有NULL的情况下，会出现没有行返回的情况，自己遇到过几次，好几次没有转过弯来。
今天记录一下，避免以后再犯类似错误！
1.建立环境:
select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
drop table t1 purge;
drop table t2 purge;
create table t1 as select rownum id ,lpad('t1',6,'x') v1 from dual connect by level
create table t2 as select rownum+1 id ,lpad('t2',6,'x') v1 from dual connect by level
2.测试
SQL> select t1.* from t1 where t1.id not in ( select id from t2 );
ID V1
---------- ------
1 xxxxt1
10 xxxxt1
select t1.* from t1 where not exists ( select 1 from t2 where t2.id=t1.id);
ID V1
---------- ------
1 xxxxt1
10 xxxxt1
--如果插入NULL到T2后呢?
insert into t2 values (NULL,'t2yyyy');
commit;
SQL> select t1.* from t1 where t1.id not in ( select id from t2 );
no rows selected
--???没有行返回!
SQL> select t1.* from t1 where not exists ( select 1 from t2 where t2.id=t1.id);
ID V1
---------- ------
1 xxxxt1
10 xxxxt1
在使用not in时，如果子查询的结果包含 NULL, NULL表示未知，not in里面包含NULL表示不在NULL里面，这样的查询结果也是未知，
这样主查询返回的结果为0。
select t1.* from t1 where t1.id not in ( NULL，1 );
要避免这个错误，修改如下：
SQL> select t1.* from t1 where t1.id not in ( select id from t2 where id is not null );
ID V1
---------- ------
1 xxxxt1
10 xxxxt1
3.看看执行计划:
SQL> select t1.* from t1 where t1.id not in ( select id from t2 where id is not null );
ID V1
---------- ------
1 xxxxt1
10 xxxxt1
SQL> @dpc
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID cuy5z0y79wrn8, child number 0
-------------------------------------
select t1.* from t1 where t1.id not in ( select id from t2 where id is
not null )
Plan hash value: 1270581391
------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 8 (100)| | | |
|* 1 | HASH JOIN ANTI SNA| | 10 | 8 (13)| 1180K| 1180K| 1425K (0)|
| 2 | TABLE ACCESS FULL| T1 | 10 | 3 (0)| | | |
|* 3 | TABLE ACCESS FULL| T2 | 8 | 4 (0)| | | |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="ID")
3 - filter("ID" IS NOT NULL)
-- HASH JOIN ANTI SNA表示什么意思? SNA表示什么?
SQL> select t1.* from t1 where t1.id not in ( select id from t2 );
no rows selected
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 3u24wpavnkahc, child number 0
-------------------------------------
select t1.* from t1 where t1.id not in ( select id from t2 )
Plan hash value: 1275484728
------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 8 (100)| | | |
|* 1 | HASH JOIN ANTI NA | | 10 | 8 (13)| 1180K| 1180K| 1129K (0)|
| 2 | TABLE ACCESS FULL| T1 | 10 | 3 (0)| | | |
| 3 | TABLE ACCESS FULL| T2 | 9 | 4 (0)| | | |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="ID")
-- HASH JOIN ANTI NA表示什么意思? NA表示什么?