一、not in/not exists+null
SQL> create table usertable (userid integer,username varchar2(10));
SQL> create table usergrade (userid integer,username varchar2(10),grade number);
SQL> insert into usertable values (1,‘user1‘);
SQL> insert into usertable values (2,null);
SQL> insert into usertable values (3,‘user3‘);
SQL> insert into usertable values (4,null);
SQL> insert into usertable values (5,‘user5‘);
SQL> insert into usertable values (6,‘user6‘);
SQL> insert into usergrade values (1,‘user1‘,90);
SQL> insert into usergrade values (2,null,90);
SQL> insert into usergrade values (7,‘user7‘,90);
SQL> insert into usergrade values (8,‘user8‘,90);
SQL> select count(*)
from usergrade
where username not in (select username from usertable);
count(*)
----------
0
SQL> select count(*)
from usergrade g
where not exists (select null
from usertable t
where t.userid=g.userid and t.username=g.username);
count(*)
----------
3
SQL>select * from dual where null = null;
SQL>select * from dual where null <> null;
SQL>select * from dual where null is null;
--有结果，null is null不是比较，而是说null 是个null
SQL>select * from dual where null in (select null from dual);
SQL>select * from dual where dummy not in (select null from dual);
-- not in实际上是用!=依次比较列表，然后去and，TRUE AND NULL = NULL。只要列表包括null值，就会返回FALSE
SQL>select * from dual where dummy in (null,‘X‘); --有结果
-- in后里面的列表可以包括null，但是因为相当于用=依次比较，然后去or，true or null = true，所以他们是被忽视的
|
