用LEFT JOIN代替NOT IN或NOT EXISTS
Sql代码 www.2cto.com
用左连接，结果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