Oracle 中不使用NOT IN 和 NOT EXISTS的另一種方法

   用LEFT JOIN 代替NOT IN 或 NOT EXISTS：

SQL> conn scott/tiger
Connected.
SQL> CREATE TABLE testa
  2  (
  3   id number,
  4   value varchar2(10)
  5  );

Table created.

SQL> INSERT INTO testa VALUES(1,'a');

1 row created.

SQL> INSERT INTO testa VALUES(2,'b');

1 row created.

SQL> INSERT INTO testa VALUES(3,'c');

1 row created.

SQL> INSERT INTO testa VALUES(4,'d');

1 row created.

SQL> INSERT INTO testa VALUES(5,'e');

1 row created.

SQL> INSERT INTO testa VALUES(6,'f');

1 row created.

SQL> COMMIT;

Commit complete.
SQL> CREATE TABLE testb AS
  2  SELECT * FROM testa WHERE 1=0;

Table created.

SQL> INSERT INTO testb VALUES(2,'b');

1 row created.

SQL> INSERT INTO testb VALUES(4,'d');

1 row created.

SQL> INSERT INTO testb VALUES(6,'f');

1 row created.

SQL> COMMIT;

Commit complete.

用左連線，結果testb表裡TempColum的值為NULL：

SQL> SELECT a.*,b.id "TempColum" FROM testa a LEFT JOIN testb b ON a.id=b.id;

        ID   VALUE       TempColum
---------- ----------       ----------
         2       b                   2
         4       d                   4
         6       f                    6
         5       e                   NULL 
         3       c                   NULL 
         1       a                   NULL 

6 rows selected.

將NULL值過濾出來就是最後需要的結果：

SQL>

SELECT c.id,c.value FROM
(
 SELECT a.*,b.id "TempColum" FROM testa a LEFT JOIN testb b ON a.id=b.id
) c
WHERE c."TempColum" IS NULL
ORDER BY c.id

SQL>
        ID VALUE
---------- ----------
         1 a
         3 c
         5 e

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16400082/viewspace-692708/，如需轉載，請註明出處，否則將追究法律責任。