oracle中 使用不了,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