not in/not exists+null

转载 2007年09月17日 21:15:00

根据如下表的查询结果,那么以下语句的结果是(知识点:not in / not exists + null)

SQL
> select * from usertable ;

USERID           USERNAME
-----------      ----------------

     
1          user1

      2          null

      3          user3

      4          null

      5          user5

      6          user6
     

SQL
> select * from usergrade ;

USERID         USERNAME           GRADE
----------     ----------------   ----------

      
1       user1              90

      2        null               80

      7        user7              80

      8        user8              90

执行语句:

select count
(*) from usergrade where username not in ( select username from usertable );



select count (*) from usergrade g where not exists

( select null from usertable t where t . userid = g . userid and t . username = g . username );



结果为:语句1 ( A  )    语句2   ( D )


A : 0     B : 1     C : 2     D : 3      E : NULL

原因:如果用NOT IN SUBQUERY ,SUBQUERY查询出来结果中含有NULL值的话。整个NOT IN SUBQUERY 为FALSE。null 与 别的数据的集合还是null。

当看完下面的问答就明白了。把条件代入实际的数值分析就可以得到。同理可推出EXISTS。

Reasons.

The main reason behind returning no rows is because the subquery may contain a "Null" value. If the subquery contains a Null value then the Query does not return a value when it is used in a "Not in" clause. But this is Not a Bug but a functionality .

Let us consider a table "Table1" containing a column "A"       

Create table Table1 (A number);
        
Insert into Table1 Values(
1);        

Insert into Table1 Values( 2);

Let us consider another table "Table2" Containing a column "A"

        

Create table Table2 (A number);
Insert into Table2 Values(
1);  

Insert into Table2 Values( Null);

Table2 contains a Null value for the Column "A"

If I write a Query with a Not in Clause using a Subquery of Table2 will lead to No rows even though rows can be returned.       

Select A  
from
 Table1 Where A not in (Select A from Table2);

Even though "2" is not in Table2 but in Table1, returns Nothing as Table2 Subquery involves a Null in the "Not in" clause.

 

相关文章推荐

内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:not in/not exists+null
举报原因:
原因补充:

(最多只允许输入30个字)