简单用两个例子说明一下exists和not exists的用法.

SQL> select * from tt2;

        ID NAME
---------- --------------------
       100 EDITION
       116 TABLE
       280 SYNONYM
      1098 SEQUENCE
      1279 PACKAGE
      1300 VIEW

6 rows selected.

SQL> select * from tt1;

        ID NAME
---------- --------------------
       100 EDITION
       116 TABLE
       117 SYNONYM


SQL> select * from tt2 where name in(select name from tt1);

        ID NAME
---------- --------------------
       100 EDITION
       116 TABLE
       280 SYNONYM

SQL> select * from tt2 where exists (select name from tt1);     #tt1查询出来的为true，故tt2所有值返回

        ID NAME
---------- --------------------
       100 EDITION
       116 TABLE
       280 SYNONYM
      1098 SEQUENCE
      1279 PACKAGE
      1300 VIEW

6 rows selected.

SQL> select * from tt2 where exists (select name from tt1 where tt1.name=tt2.name);    #tt1连接tt2后查询出来的为true，故tt2所有值返回

        ID NAME
---------- --------------------
       100 EDITION
       116 TABLE
       280 SYNONYM

SQL> select * from tt2 where not exists (select * from tt1);

no rows selected

SQL> select * from tt2 where not exists (select * from tt1 where tt1.name=tt2.name);

        ID NAME
---------- ----------------------------------------
      1279 PACKAGE
      1300 VIEW
      1098 SEQUENCE