[20121028]not in與NULL問題.txt

[20121028]not in與NULL問題.txt

在sql語句中使用not in，在遇到子表含有NULL的情況下，會出現沒有行返回的情況，自己遇到過幾次，好幾次沒有轉過彎來。

今天記錄一下，避免以後再犯類似錯誤！

1.建立環境:

select * from v$version where rownum<=1;

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<=10;

create table t2 as select rownum+1 id ,lpad('t2',6,'x') v1 from dual connect by level<=8;

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表示什麼?