oracle三个表in查询,ORACLE NOT IN查询不到应有的结果的分析

在ORACLE实际的数据应用中，发现SQL语句中使用NOT IN

进行条件筛选数据时，当子查询中存在NULL数据时，会存在查询不到数据的情况，对此问题，本文采用实例测试的方式来测试这种情况，并针对出现这种情况时，该采用什么方法来处理做一定介绍。

一、创建测试表和添加测试数据

create table t_test01(tid number,user_name

varchar2(20));

insert into t_test01

select 1,'jiajia' from dual

union all

select 2,'maimai' from dual

union all

select 3,'qianqian' from dual

;

commit;

--查看数据

select * from t_test01;

----  ----------

TID USER_NAME

1 jiajia

2 maimai

3 qianqian

create table t_test02(tid number,user_name

varchar2(20));

--添加测试数据

insert into t_test02

select 1,'huahua' from dual

union all

select 2,'yangyang' from dual

union all

select null,'qianqian' from dual

;

commit;

--查看数据

select * from t_test02;

----  ----------

TID USER_NAME

1 huahua

2 yangyang

qianqian

二、处理过程

1、采用not in查询

select *

from  t_test01

where  tid not in (select tid from

t_test02);

-----  ---------------

TID USER_NAME

说明：这样查询，本来应该有一条数据，结果没有。

2、采用not exists查询

select *

from  t_test01 a

where  not exists (select 1 from t_test02 b

where b.tid=a.tid);

-----  ---------------

TID USER_NAME

3 qianqian

说明：这样结果就正确，有一条数据显示。

3、采用子查询中关联条件查询

select *

from  t_test01 a

where  a.tid not in (select b.tid from

t_test02 b where a.tid=b.tid);

-----  ---------------

TID USER_NAME

3 qianqian

说明：这样结果就正确，有一条数据显示。

三、原因分析

经过一番搜索分析得知，是因为子查询里面有空集。即子查询的结果集里面有NULL的结果导致使用not

in时查询不到数据。

四、优化查询方法

1、采用not in 时子查询中需要加is not

null条件

select *

from  t_test01

where  tid not in (select tid from t_test02

where tid is not null);

----  ---------------

TID USER_NAME

3 qianqian

2、采用采用not in 时子查询中需要关联条件查询

select *

from  t_test01 a

where  a.tid not in (select b.tid from

t_test02 b where a.tid=b.tid);

----  ---------------

TID USER_NAME

3 qianqian

五、优化总结

这类查询时，尽量采用not exists的处理方法，避免因疏忽而导致不必要的结果错误。

六、附录

针对本文题的分析:

1、首先来说说Oracle中的NULL

Oracle中的NULL代表的是无意义，或者没有值。将NULL和其他的值进行逻辑运算，运算过程中，NULL的表现更象是FALSE。

下面请看真值表：

AND NULL

OR  NULL

TRUE  NULL  TRUE

FALSE  FALSE  NULL

NULL  NULL

NULL

另外，NULL和其他的值进行比较或者算术运算(、=、!=、+、-、*、/)，结果仍是NULL。

如果想要判定某个值是否为NULL，可以用IS NULL或者IS NOT NULL。

2、再来说说Oracle中的IN

in是一个成员条件, 对于给定的一个集合或者子查询,它会比较每一个成员值。

IN功能上相当于 =ANY 的操作，而NOT IN 功能上相当于 !=ALL 的操作。

IN在逻辑上实际上就是对给定的成员集合或者子查询结果集进行逐条的判定，例如：

SELECT * FROM table1 A WHERE A.col1 in (20,50,NULL);

实际上就是执行了

SELECT * FROM table1 A WHERE A.col1=20 OR A.col1=50 OR

A.col1=NULL;

这样，根据NULL的运算特点和真值表，我们可以看出，上边这个WHERE

字句可以被简化(如果返回NULL则无结果集返回，这一点和FALSE是一样的)为WHERE A.col1=20 OR

A.col1=50

也就是说，如果你的table1中真的存在含有NULL值的col1列，则执行该语句，无法查询出那些值为null的记录。

再来看看NOT IN。根据逻辑运算关系，我们知道，NOT (X=Y OR N=M) 等价于 X!=Y AND

N!=M，那么：

SELECT * FROM table1 A WHERE A.col1 not in (20,50,NULL)

等价于SELECT * FROM table1 A WHERE A.col1!=20 AND A.col1!=50 AND

A.col1!=NULL

根据NULL的运算特性和真值表，该语句无论前两个判定条件是否为真，其结果一定是NULL或者FALSE。故绝对没有任何记录可以返回。

这就是为什么语句1查不到应有结果的原因。当然，如果你用NOT

IN的时候，预先在子查询里把NULL去掉的话，那就没问题了。

有些朋友可能要问了：那如果我想把A表里面那些和B表一样col1列的值一样的记录都查出来，即便A、B两表里面的col1列都包括值为NULL的记录的话，用这一条语句就没办法了吗？

我只能很遗憾的告诉你，如果你想在WHERE后面单纯用IN

似乎不太可能了，当然，你可以在外部的查询语句中将NULL条件并列进去，

例如：

SELECT * FROM table1 A WHERE A.col1 in (SELECT B.col1 FROM

table2 B) OR A.col1 IS NULL;

3、最后谈谈EXISTS

有人说EXISTS的性能比IN要好。但这是很片面的。我们来看看EXISTS的执行过程：

select * from t1 where exists ( select * from t2 where t2.col1

= t1.col1 )

相当于：

for x in ( select * from t1 )

loop

if (

exists ( select * from t2 where t2.col1 = x.col1 )

then

OUTPUT THE RECORD in x

end

if

end loop

也就是说，EXISTS语句实际上是通过循环外部查询的结果集，来过滤出符合子查询标准的结果集。于是外部查询的结果集数量对该语

句执行性能影响最大，故如果外部查询的结果集数量庞大，用EXISTS语句的性能也不一定就会好很多。

当然，有人说NOT IN是对外部查询和子查询都做了全表扫描，如果有索引的话，还用不上索引，但是NOT

EXISTS是做连接查询，所以如果连接查询的两列都做了索引，性能会有一定的提升。当然至于实际的查询效率，我想还是具体情况具体分析吧。

那么我们不妨来分析一下语句2为什么能够的到正确的结果吧：

not exists 是这样的：

select * from table1 A where not exists (SELECT B.col1 FROM

table2 B where B.col1 = A.col1)

实际上是这样的执行过程：

for x in ( select * from table1 A )

loop

if

(not exists ( select * from table2 B where B.col1 = x.col1 )

then

OUTPUT THE RECORD in x

end

if

end loop

由于表A中不包含NULL的记录，所以，遍历完表A，也只能挑出表A中独有的记录，这就是为什么语句2能够完成语句3的任务的原因。

如果表A中存在NULL记录而表B中不存在，采用not exists能查询出来吗？

答案：A表中的NULL也会被查出来。因为select * from table2 B where B.col1 =

NULL不返回结果，故

not exists ( select * from table2 B where B.col1 = x.col1

)的值为真。

本文主要参考资料：http://www.cnblogs.com/yjhrem/articles/2387648.html和其他网络资料，经总结实际测试而来。