not in/not exists+null

根据如下表的查询结果，那么以下语句的结果是（知识点：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.