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