问题:语句1 :
Select * from table1 A where A.col1 not in ( select col1 from table2 B )
如果这样,本来应该有一条数据,结果没有。 如果我改写成这样: 语句2 :
select * from table1 A where not exists ( SELECT * FROM table2 B where B.col1 = A.col1)
结果就正确,有一条数据显示。
经过一番搜索,原以为是子查询结果集太大的原因。 后来有网上强人指点:子查询里面有空集。即子查询的结果集里面有NULL的结果。 把查询语句修改成: 语句3 : Select * from table1 A where A.col1 not in ( select col1 from table2 B where B.col1 is not null )
果然就查出来了。而且一点不差。。。厉害阿~~~
下面是针对本文题的分析:1。 首先来说说Oracle中的NULL。Oracle中的NULL代表的是无意义,或者没有值。将NULL和其他的值进行逻辑运算,运算过程中,NULL的表现更象是FALSE。下面请看真值表:AND NULL | OR NULL | |
TRUE | NULL | TRUE |
FALSE | FALSE | NULL |
NULL | NULL | NULL |
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去掉的话,那就没问题了,例如语句3 。有些童鞋可能要问了:那如果我想把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为什么能够的到正确的结果吧:语句2是这样的: 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中不存在呢?答案:A表中的NULL也会被查出来。因为select * from table2 B where B.col1 = NULL不返回结果,故not exists ( select * from table2 B where B.col1 = x.col1 )的值为真。