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

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;

SELECT *

FROM rl_test_a

WHERE (id,name) NOT IN

(SELECT id,name

FROM 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);

SELECT *

FROM a

WHERE

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

AND

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

从上面的分析可以看出，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的问题。

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

);

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

);

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

);

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

1.jpg

2.jpg

3.jpg

4.jpg

5.jpg

• 博文量
8
• 访问量
13669