同时应尽可能使用NOT EXISTS来代替NOT IN,尽管二者都使用了NOT(不能使用索引而降低速度),但NOT EXISTS要比NOT IN查询效率更高。
以下的null代表真的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( 0 ) 语句2 ( 3 )
A: 0 B:1 C:2 D:3 E:NULL
2
在以下的表的显示结果中,以下语句的执行结果是(知识点:in/exists+rownum)
SQL> select * from usertable;
USERID USERNAME
----------- ----------------
1 user1
2 user2
3 user3
4 user4
5 user5
SQL> select * from usergrade;
USERNAME GRADE
---------------- ----------
user9 90
user8 80
user7 80
user2 90
user1 100
user1 80
执行语句
Select count(*) from usertable t1 where username in
(select username from usergrade t2 where rownum <=1);
Select count(*) from usertable t1 where exists
(select 'x' from usergrade t2 where t1.username=t2.username and rownum <=1);
以上语句的执行结果是:( ) ( )
A: 0 B: 1 C: 2 D: 3