ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [转]复合列NULL问题研究

[转]复合列NULL问题研究

原创 Linux操作系统 作者:ycpriscilla 时间:2011-08-31 22:07:47 0 删除 编辑

牛人之所以牛,是因为其注重细节。今天起转的贴都加上[转]

原文:http://www.itpub.net/viewthread.php?tid=1325582&extra=page%3D1%26amp%3Bfilter%3Ddigest

[quote]原帖由 [i]dingjun123[/i] 于 2010-7-14 13:49 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=16114185&ptid=1325582][img]http://www.itpub.net/images/common/back.gif[/img][/url]
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,因为23的已经是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子查询,大家都很清楚,但是对于复合列的,很多人就不知道原因了,所以总结了一下,以便学习。 [/quote]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24478467/viewspace-706381/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle 10.2 重建em
请登录后发表评论 登录
全部评论

注册时间:2011-08-18

  • 博文量
    36
  • 访问量
    103588