复合列NULL问题研究

dingjun123 · 发表于 2010-7-14 13:49

IN子查询相当于OR条件，根据NULL的逻辑运算规则，哪个条件为TRUE的行就返回那个行，很简单，主要说NOT IN

-----------------------------------Q1：单列NOT IN子查询中有NULL的分析---------------------------------

drop table test1;
drop table test2;
create table test1
(id number);
create table test2
(id number);
insert into test1 values(1);
insert into test1 values(2);
insert into test2 values(null);
insert into test2 values(1);
commit;
--选出在test1中不在test2中的行记录
--单列的，常见错误如下，没有结果：

SQL> select id from test1
  2  where id not in (select id from test2);

      ID
----------

-正确写法,常见的还是not exists：
SQL> select id from test1
  2  where id not in (select id from test2 where test1.id=test2.id);

      ID
----------
      2

SQL> select id from test1
  2  where  not exists (select 1 from test2 where test1.id=test2.id);

      ID
----------
      2

------------------------------------------------------------Q1结论------------------------------------------------
/**
Q1的问题很简单，单列的NULL，如果非相关子查询的结果有NULL，那么整个条件为FALSE/UNKNOWN，也就是没有结果的原因，如果深入分析下，等价于
SELECT .... WHERE ID <> NULL AND ID <>....
根据NULL的比较和逻辑运算规则，可以知道整个条件要么是false，要么是unknown,所以没有结果
**/

--Q1开始的语句等价于
SQL> select id from test1
  2  where id <> null and  id <> 1;

      ID
----------

----------------------------------------Q2：复合列NOT IN子查询有NULL的分析-----------------------------

--复合列子查询比上面说的单列子查询就复杂多了，见下面详细分析：
drop table t1;
drop table t2;
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表中的记录
--常见错误结果，和Q1一样，没有结果
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;

1 row deleted
SQL> insert into t2 values(null,3);

1 row inserted
SQL> commit;

Commit complete

--现在呢？？正确返回了
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

---------------------------------------------Q2结论-----------------------------------------------------
/**
根据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，返回结果，也就说明了为什么Q2中的
测试是那样的结果
**/

看个简单的结果：
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

综上所述，对于NULL的问题还是需要特别留心的，对于单列NOT IN子查询，大家都很清楚，但是对于复合列的，很多人就不知道原因了，所以总结了一下，以便学习。

[ 本帖最后由 dingjun123 于 2010-7-14 13:53 编辑 ]