[转]复合列NULL问题研究

最新推荐文章于 2024-11-12 17:21:52 发布

cuipangzhong3835

最新推荐文章于 2024-11-12 17:21:52 发布

阅读量78

点赞数

文章标签： php

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

原文：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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24478467/viewspace-706381/