not exists and not in compare

SQL> select * from tbl_test1;
COL1       COL2
———- ———-
2          2
SQL> select * from tbl_test;
COL1       COL2
———- ———-
1          2
2          2
2
SQL> rollback;
Rollback complete.
SQL> select * from tbl_test1;
COL1       COL2
———- ———-
2
3          2
2          2
SQL> select * from tbl_test;
COL1       COL2
———- ———-
1          2
2          2
2
SQL> delete from tbl_Test1 where col1 not in (select col1 from tbl_Test);
0 rows deleted.
SQL> delete from tbl_Test1 t1 where  not exists (select col1 from tbl_Test t2 where t1.col1=t2.col1) ;
2 rows deleted.
SQL> select * from tbl_test;
COL1       COL2
———- ———-
1          2
2          2
2
SQL> select * from tbl_test1;
COL1       COL2
———- ———-
2          2
SQL> rollback;
Rollback complete.
SQL> explain plan for delete from tbl_Test1 where col1 not in (select col1 from tbl_Test);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
——————————————————————————————————————————————–
Plan hash value: 3192674508
———————————————————————————
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————
|   0 | DELETE STATEMENT    |           |     1 |    13 |     4   (0)| 00:00:01 |
|   1 |  DELETE             | TBL_TEST1 |       |       |            |          |
|*  2 |   FILTER            |           |       |       |            |          |
|   3 |    TABLE ACCESS FULL| TBL_TEST1 |     3 |    39 |     2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| TBL_TEST  |     3 |    39 |     2   (0)| 00:00:01 |
———————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – filter( NOT EXISTS (SELECT /*+ */ 0 FROM “TBL_TEST” “TBL_TEST”
WHERE LNNVL(“COL1″<>:B1)))
4 – filter(LNNVL(“COL1″<>:B1))
Note
—–
- dynamic sampling used for this statement
22 rows selected.
SQL> explain delete from tbl_Test1 t1 where  not exists (select col1 from tbl_Test t2 where t1.col1=t2.col1) ;
explain delete from tbl_Test1 t1 where  not exists (select col1 from tbl_Test t2 where t1.col1=t2.col1)
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> explain plan for delete from tbl_Test1 t1 where  not exists (select col1 from tbl_Test t2 where t1.col1=t2.col1) ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
——————————————————————————————————————————————–
Plan hash value: 1337621389
———————————————————————————
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————
|   0 | DELETE STATEMENT    |           |     2 |    52 |     5  (20)| 00:00:01 |
|   1 |  DELETE             | TBL_TEST1 |       |       |            |          |
|*  2 |   HASH JOIN ANTI    |           |     2 |    52 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TBL_TEST1 |     3 |    39 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TBL_TEST  |     3 |    39 |     2   (0)| 00:00:01 |
———————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“T1″.”COL1″=”T2″.”COL1″)
Note
—–
- dynamic sampling used for this statement
20 rows selected.

SQL> select * from tbl_test1;
COL1       COL2———- ———-         2          2
SQL> select * from tbl_test;
COL1       COL2———- ———-         1          2         2          2                    2
SQL> rollback;
Rollback complete.
SQL> select * from tbl_test1;
COL1       COL2———- ———-                    2         3          2         2          2
SQL> select * from tbl_test;
COL1       COL2———- ———-         1          2         2          2                    2
SQL> delete from tbl_Test1 where col1 not in (select col1 from tbl_Test);
0 rows deleted.
SQL> delete from tbl_Test1 t1 where  not exists (select col1 from tbl_Test t2 where t1.col1=t2.col1) ;
2 rows deleted.
SQL> select * from tbl_test;
COL1       COL2———- ———-         1          2         2          2                    2
SQL> select * from tbl_test1;
COL1       COL2———- ———-         2          2
SQL> rollback;
Rollback complete.
SQL> explain plan for delete from tbl_Test1 where col1 not in (select col1 from tbl_Test);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT——————————————————————————————————————————————–Plan hash value: 3192674508
———————————————————————————| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |———————————————————————————|   0 | DELETE STATEMENT    |           |     1 |    13 |     4   (0)| 00:00:01 ||   1 |  DELETE             | TBL_TEST1 |       |       |            |          ||*  2 |   FILTER            |           |       |       |            |          ||   3 |    TABLE ACCESS FULL| TBL_TEST1 |     3 |    39 |     2   (0)| 00:00:01 ||*  4 |    TABLE ACCESS FULL| TBL_TEST  |     3 |    39 |     2   (0)| 00:00:01 |———————————————————————————
Predicate Information (identified by operation id):—————————————————
2 – filter( NOT EXISTS (SELECT /*+ */ 0 FROM “TBL_TEST” “TBL_TEST”              WHERE LNNVL(“COL1″<>:B1)))   4 – filter(LNNVL(“COL1″<>:B1))
Note—–   – dynamic sampling used for this statement
22 rows selected.
SQL> explain delete from tbl_Test1 t1 where  not exists (select col1 from tbl_Test t2 where t1.col1=t2.col1) ;explain delete from tbl_Test1 t1 where  not exists (select col1 from tbl_Test t2 where t1.col1=t2.col1)        *ERROR at line 1:ORA-00905: missing keyword

SQL> explain plan for delete from tbl_Test1 t1 where  not exists (select col1 from tbl_Test t2 where t1.col1=t2.col1) ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT——————————————————————————————————————————————–Plan hash value: 1337621389
———————————————————————————| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |———————————————————————————|   0 | DELETE STATEMENT    |           |     2 |    52 |     5  (20)| 00:00:01 ||   1 |  DELETE             | TBL_TEST1 |       |       |            |          ||*  2 |   HASH JOIN ANTI    |           |     2 |    52 |     5  (20)| 00:00:01 ||   3 |    TABLE ACCESS FULL| TBL_TEST1 |     3 |    39 |     2   (0)| 00:00:01 ||   4 |    TABLE ACCESS FULL| TBL_TEST  |     3 |    39 |     2   (0)| 00:00:01 |———————————————————————————
Predicate Information (identified by operation id):—————————————————
2 – access(“T1″.”COL1″=”T2″.”COL1″)
Note—–   – dynamic sampling used for this statement
20 rows selected.

not in和not exists通用的前提是第二个表的被关联字段非空

从理论上看，not exists比not in的效率要高。