NOT IN 非相关子查询中注意NULL.docx-CSDN博客

资料来自剑破冰山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表中的id＝2的行。使用NOT IN子查询看看。

SELECT *

FROM rl_test_a

WHERE (id,name) NOT IN

(SELECT id,name

FROM rl_test_b

);

注：这个是非相关子查询,rl_test_a和rl_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；除此之外，(NOT、AND、TRUE)有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表中的数据呢？这里要将id为NULL看成是相等的，用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;

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

);

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_a和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(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 IN、EXISTS/NOT EXISTS的相关子查询。

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

转载于:http://blog.itpub.net/24025515/viewspace-718963/