<> 与not exists 微妙之处

Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0
Connected as bibase

SQL> create table a
  2   ( a    char(1),
  3      b    char(2)
  4  );

Table created

SQL> insert into a values (1,2);

1 row inserted

SQL>  insert into a values ('',3);

1 row inserted

SQL> insert into a values ('','');

1 row inserted

SQL> select count(*) from a;

  COUNT(*)
----------
         3

SQL>  select count(*) from a where not exists (select null from a where a=1);

  COUNT(*)
----------
         0

SQL>  select count(*) from a where a<>1;

  COUNT(*)
----------
         0

SQL>  update a set a=''; --关键点就在与此

3 rows updated

SQL> commit;

Commit complete

SQL> select * from a;

A B
- --
  2
  3

区别点如下（都是现实A为空的记录数）：
SQL>  select count(*) from a where not exists (select null from a where a=1);

  COUNT(*)
----------
         3

SQL> select count(*) from a where a<>1;

  COUNT(*)
----------
         0

SQL> select * from  v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE        9.2.0.8.0        Production

TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13159425/viewspace-663960/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13159425/viewspace-663960/