用LEFT JOIN 代替NOT IN 或 NOT EXISTS

用左连接，结果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