查看: 40350|回复: 72

[精华] 复合列NULL问题研究

[复制链接]
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 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 编辑 ]
论坛徽章:
40
授权会员
日期:2009-03-04 17:06:25最佳人气徽章
日期:2013-03-19 17:24:25SQL极客
日期:2013-12-09 14:13:35优秀写手
日期:2013-12-18 09:29:09ITPUB元老
日期:2015-03-04 13:33:34白羊座
日期:2016-03-11 13:49:34乌索普
日期:2017-11-17 11:40:00
发表于 2010-7-14 13:59 | 显示全部楼层
顶一下,
对于复合列的情况确实没有注意过

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
 楼主| 发表于 2010-7-14 14:01 | 显示全部楼层
复合列的问题是上次在csdn上有人问,但是别人都是用单列的说明的,我后来试着改了下数据,就有结果了,所以就拆开分析,这样就比较好懂了

使用道具 举报

回复
认证徽章
论坛徽章:
176
现任管理团队成员
日期:2011-05-07 01:45:08版主7段
日期:2012-07-05 02:21:03ITPUB长老会成员
日期:2015-05-07 15:11:10ITPUB年度最佳版主
日期:2011-04-08 18:37:09ITPUB年度最佳版主
日期:2011-12-28 15:24:18ITPUB牛人
日期:2010-10-25 12:41:322010数据库技术大会纪念徽章
日期:2015-04-23 10:33:192011数据库大会纪念章
日期:2015-04-23 10:33:192012数据库大会纪念章
日期:2015-04-23 10:33:192013数据库大会纪念章
日期:2015-04-23 10:33:19
发表于 2010-7-14 14:04 | 显示全部楼层
呵呵,好贴~

使用道具 举报

回复
论坛徽章:
32
奥运会纪念徽章:摔跤
日期:2012-08-23 11:03:05青年奥林匹克运动会-击剑
日期:2014-09-19 10:58:152014年世界杯参赛球队:巴西
日期:2014-07-07 12:19:232014年世界杯参赛球队: 瑞士
日期:2014-05-19 12:18:36马上有钱
日期:2014-04-08 12:12:232014年新春福章
日期:2014-04-04 14:20:47马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09红旗
日期:2014-02-14 15:15:55优秀写手
日期:2013-12-18 09:29:16
发表于 2010-7-14 14:09 | 显示全部楼层
不错,丁俊版主总是那么的细心和无私。。。
学习了

使用道具 举报

回复
求职 : 数据库开发
认证徽章
论坛徽章:
28
ITPUB学员
日期:2009-10-14 18:49:45至尊黑钻
日期:2015-12-31 11:11:56数据库板块每日发贴之星
日期:2009-10-22 01:01:02优秀写手
日期:2014-04-30 06:00:17ITPUB8周年纪念徽章
日期:2009-10-09 21:30:10马上有车
日期:2014-10-09 10:14:53马上有钱
日期:2014-02-18 16:43:09路虎
日期:2013-10-15 15:38:59林肯
日期:2013-09-12 15:57:33ITPUB 11周年纪念徽章
日期:2012-10-09 18:11:48
发表于 2010-7-14 14:18 | 显示全部楼层
学习,支持一下

使用道具 举报

回复
认证徽章
论坛徽章:
1682
九尾狐狸
日期:2012-09-19 11:12:55九尾狐狸
日期:2012-09-19 11:12:55九尾狐狸
日期:2012-09-27 15:37:10九尾狐狸
日期:2012-09-19 11:12:55九尾狐狸
日期:2012-09-19 11:12:55九尾狐狸
日期:2012-09-19 11:12:55九尾狐狸
日期:2012-09-19 11:12:55九尾狐狸
日期:2012-09-19 11:12:55玉石琵琶
日期:2014-06-26 16:52:29玉石琵琶
日期:2014-06-26 16:52:29
发表于 2010-7-14 14:32 | 显示全部楼层

使用道具 举报

回复
论坛徽章:
2
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:42:49
发表于 2010-7-14 16:40 | 显示全部楼层
通常我们用的是外关联,例如:
要找到在test1中存在但是test2不存在的记录
select a.*
from  test1 a,
test2 b
where a.id=b.id(+)
and b.id is null ;

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
 楼主| 发表于 2010-7-14 16:55 | 显示全部楼层
原帖由 凝结水晶 于 2010-7-14 16:40 发表
通常我们用的是外关联,例如:
要找到在test1中存在但是test2不存在的记录
select a.*
from  test1 a,
test2 b
where a.id=b.id(+)
and b.id is null ;

这种写法经常会让人看不懂,复合列的话更难懂

使用道具 举报

回复
论坛徽章:
32
奥运会纪念徽章:摔跤
日期:2012-08-23 11:03:05青年奥林匹克运动会-击剑
日期:2014-09-19 10:58:152014年世界杯参赛球队:巴西
日期:2014-07-07 12:19:232014年世界杯参赛球队: 瑞士
日期:2014-05-19 12:18:36马上有钱
日期:2014-04-08 12:12:232014年新春福章
日期:2014-04-04 14:20:47马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09红旗
日期:2014-02-14 15:15:55优秀写手
日期:2013-12-18 09:29:16
发表于 2010-7-14 19:52 | 显示全部楼层
外关联用的还是比较多的

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表