[Oracle] exists 和 not exists

簡單用兩個例子說明一下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