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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16400082/viewspace-692708/