复合列的not in的问题

create table t1(a number,b number);
create table t2(a number,b number);
insert into t1 values(1,1);
insert into t1 values(1,2);
insert into t2 values(1,1);
insert into t2 values(null,2);
commit;

--同样，查询t1的(a,b)同时满足不在t2表中的记录
SQL> select * from t1
  2   where (a,b) not in (select a,b from t2);

         A          B
---------- ----------

--同样用相关子查询改写则正确，结果省略
select * from t1
where (a,b) not in (select a,b from t2 where t1.a=t2.a and t1.b=t2.b);
select * from t1
where   not exists (select 1 from t2 where t1.a=t2.a and t1.b=t2.b);

----分析如下：因为是复合列，相当于列的组合条件是or,只要有一个列不满足条件，就应该返回那个记录

--数据改变下
SQL> delete from t2 where a is null;
SQL> insert into t2 values(null,3);
SQL> commit;
SQL> select * from t1
  2   where (a,b) not in (select a,b from t2);

         A          B
---------- ----------
         1          2

--用前面的分析改写，等价于上面的语句
SQL> select * from t1
  2   where (a <> null or b <> 3)
  3  and (a <>1 or b <> 1);

         A          B
---------- ----------
         1          2

/**
根据NULL的比较和逻辑运算规则，OR条件有一个为TRUE则返回TRUE，全为FALSE则结果为FALSE，其他为UNKNOWN，比如
(1,2) not in (null,2)则相当于1 <> null or 2 <> 2,那么明显返回的结果是UNKNOWN，所以不可能为真，不返回结果，但是
(1,2) not in (null,3)相当于1 <> null or 2 <> 3,因为2<>3的已经是TRUE,所以条件为TRUE，返回结果，也就说明了为什么测试是那样的结果
**/

看个简单的结果：
SQL> SELECT * FROM DUAL WHERE (1,2) not in ( (null,2) );

DUMMY
-----
SQL> SELECT * FROM DUAL WHERE (1,2) not in ( (null,3) );

DUMMY
-----
X

转自：http://www.itpub.net/thread-1325582-1-1.html