ITPub博客

首页 > Linux操作系统 > Linux操作系统 > NOT IN 非相关子查询中注意NULL.docx

NOT IN 非相关子查询中注意NULL.docx

原创 Linux操作系统 作者:l476861438 时间:2012-03-19 10:14:41 0 删除 编辑

资料来自剑破冰山Oracle开发艺术--仅做学习使用

这里主要分析组合列NOT IN 非相关子查询中NULL的问题。对于单列,如果NOT IN非相关子查询中有NULL,则没有结果,可以使用NOT IN相关子查询、NOT EXISTS相关子查询、外连接等解决NULL的问题。

测试数据如下:

SET NULL UNKNOWN

DROP TABLE rl_test_a;

CREATE TABLE rl_test_a(id NUMBER, name VARCHAR2(10) NOT NULL);

DROP TABLE rl_test_b;

CREATE TABLE rl_test_b(id NUMBER, name VARCHAR2(10) NOT NULL);

begin

INSERT INTO rl_test_a VALUES(1,'aa');

INSERT INTO rl_test_a VALUES(2,'bb');

INSERT INTO rl_test_b VALUES(1,'aa');

INSERT INTO rl_test_b VALUES(NULL,'bb');

COMMIT;

end;

现在需要查询a表中不在b表中的记录,我希望查询的结果是a表中的id2的行。使用NOT IN子查询看看。

SELECT *

FROM rl_test_a

WHERE (id,name) NOT IN

   (SELECT id,name

    FROM rl_test_b

   );

注:这个是非相关子查询,rl_test_arl_test_b并没有进行连接。

竟然没有结果,为什么呢?分析上面的结果,上面的语句等价于:

SELECT *

FROM a

WHERE

(id,name) NOT IN (SELECT 1,'aa' FROM DUAL)

AND

(id,name) NOT IN (SELECT NULL,'bb' FROM DUAL);

对上面的SQL进行进一步的细分,等价于:

SELECT *

FROM a

WHERE

(id <> 1 OR name <> 'aa')

AND

(id <> NULL OR name <> 'bb');

现在就可以清楚地解释为什么NOT IN非相关子查询没有结果了,因为id<>1 OR name <>'aa'这个条件的结果是返回a表中的(2,'bb")行的,但是后面的条件id<>NULL OR name<>'bb'根据NULL的比较和逻辑运算规则,可以知道OR条件的两个表达式2<>NULL返回的是UNKNOWN,'bb'<>'bb'返回的是FALSE,而UNKNOWN OR FALSE的结果是UNKNOWN,所以行(2'bb')是过滤掉的,因此最终没有结果。

注:

逻辑运算的结果:OR 有一个为True结果即为True; And有一个为False即为False 除此之外,(NOTANDTRUE)UNKNOWN结果即为UNKNOWN

 

                从上面的分析可以看出,NOT IN子查询的确包含玄机,当然其他子查询也有这样的问题,比如在IN子查询中,rl_test_a表示假设(id,name)有值{(1,'aa'),(NULL,'bb')},rl_test_b表示假设(id,name)的值为{(2,'aa'),(NULL,'bb')},如何找到a表在b表中的数据呢?这里要将idNULL看成是相等的,用SELECT id,name FROM a WHERE (id,name) IN (SELECT id,name FROM b)是不可以的,必须额外考虑NULL的问题。

 

结论:在考虑求rl_test_a存在于/不存在于rl_test_b这类问题是要考虑NULL值的情况。常用的解决方法是使用相关子查询,下面举例说明:

在做测试之前,先做准备工作:SET NULL UNKNOWN

一、 只是rl_test _a中存在NULL

Truncate table rl_test_a;

Truncate table rl_test_b;

begin

INSERT INTO rl_test_a VALUES(1,'aa');

INSERT INTO rl_test_a VALUES(NULL,'bb');

INSERT INTO rl_test_b VALUES(1,'aa');

INSERT INTO rl_test_b VALUES(2,'bb');

COMMIT;

end;

1rl_test_a不存在于rl_test_b中的数据。

、使用NOT IN

select id,name

from rl_test_a a

where (a.id,a.name) not in (select b.id,b.name

from rl_test_b b

where a.id = b.id and a.name=b.name

);

Screen shot:

 

②、使用NOT EXISTS

select *

from rl_test_a a

where not exists

(select 1

from rl_test_b b

where a.id = b.id

and a.name =b.name

);

Screen shot:

2、求rl_test_a中存在于 rl_test_b中的数据。

①、使用IN

select id,name

from rl_test_a a

where (a.id,a.name) in (select b.id,b.name

                        from rl_test_b b

                        where a.id = b.id and a.name=b.name

                       );

②、使用EXISTS

select *

from rl_test_a a

where exists

(select 1

from rl_test_b b

where a.id = b.id

and a.name =b.name

);

 

二、只是在rl_test_b中存在NULL

Truncate table rl_test_a;

Truncate table rl_test_b;

begin

INSERT INTO rl_test_a VALUES(1,'aa');

INSERT INTO rl_test_a VALUES(2,'bb');

INSERT INTO rl_test_b VALUES(1,'aa');

INSERT INTO rl_test_b VALUES(NULL,'bb');

COMMIT;

end;

1、求rl_test_a中不存在于rl_test_b中的数据

①、使用NOT IN

select id,name

from rl_test_a a

where (a.id,a.name) not in (select b.id,b.name

    from rl_test_b b

    where a.id = b.id and a.name=b.name

   );

②、使用NOT EXISTS

select *

from rl_test_a a

where not exists

(select 1

from rl_test_b b

where a.id = b.id

and a.name =b.name

);

2、求rl_test_a中存在于rl_test_b中的数据

①、使用IN

select id,name

from rl_test_a a

where (a.id,a.name) in (select b.id,b.name

     from rl_test_b b

     where a.id = b.id and a.name=b.name

   );

②、使用EXISTS

select *

from rl_test_a a

where exists

(select 1

from rl_test_b b

where a.id = b.id

and a.name =b.name

);

三、在rl_test_arl_test_b中都存在NULL

Truncate table rl_test_a;

Truncate table rl_test_b;

begin

INSERT INTO rl_test_a VALUES(1,'aa');

INSERT INTO rl_test_a VALUES(NULL,'bb');

INSERT INTO rl_test_b VALUES(2,'aa');

INSERT INTO rl_test_b VALUES(NULL,'bb');

COMMIT;

end;

1、求rl_test_a中不存在于rl_test_b中的数据

select *

from rl_test_a a

where not exists

(select 1

from rl_test_b b

where decode(a.id,NULL,1,a.id) = decode(b.id,NULL,1,b.id)

and a.name =b.name

);

2、求 rl_test_a中存在于rl_test_b中数据。

select *

from rl_test_a a

where exists

(select 1

from rl_test_b b

where decode(a.id,NULL,1,a.id) = decode(b.id,NULL,1,b.id)

and a.name =b.name

);

注:1、由于WHERE NULL = NULL 返回UNKNOWN, 所以无法用in来实现(in也是在判断是不是相等)。在用NOT EXISTS/EXISTS时候,也必须使用判断,将NULL转成其他值。

2、在实际问题中多是主键判断,根据主键的非空性质,NULL问题并不是特别常见,但是也要多加注意,写成IN/NOT INEXISTS/NOT EXISTS的相关子查询。

1.jpg

2.jpg

3.jpg

4.jpg

5.jpg

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

请登录后发表评论 登录
全部评论

注册时间:2012-03-15

  • 博文量
    8
  • 访问量
    13669