What is the Difference between IN and Not IN performance wise?
If there is an index on the column the IN clause can make better use of it.
You can test this yourself:

CREATE TABLE TEST1
(
  STATUS NUMBER NOT NULL
);

CREATE INDEX IXTEST1 ON TEST1(STATUS);

insert into test1
select MOD(level,10)
from dual
connect by level &lt;= 10000;

select * from TEST1 where status in (0,1,2,3,4);
select * from TEST1 where status not in (5,6,7,8,9);


If we run oracle tuning advisor for both statements we get for the first:


-------------------------------------------------------------------------------
There are no recommendations to improve the statement.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 1479979182


-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR  |         |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IXTEST1 |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / TEST1@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("STATUS"=0 OR "STATUS"=1 OR "STATUS"=2 OR "STATUS"=3 OR 
              "STATUS"=4)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "STATUS"[NUMBER,22]
   2 - "STATUS"[NUMBER,22]

-------------------------------------------------------------------------------



but for the second:


-------------------------------------------------------------------------------
FINDINGS SECTION (5 findings)
-------------------------------------------------------------------------------

1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  Predicate "TEST1"."STATUS"5 used at line ID 1 of the execution plan is an
  inequality condition on indexed column "STATUS". This inequality condition
  prevents the optimizer from selecting indices  on table "MWARE"."TEST1".

  Recommendation
  --------------
  - Rewrite the predicate into an equivalent form to take advantage of
    indices.

2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  Predicate "TEST1"."STATUS"6 used at line ID 1 of the execution plan is an
  inequality condition on indexed column "STATUS". This inequality condition
  prevents the optimizer from selecting indices  on table "MWARE"."TEST1".

  Recommendation
  --------------
  - Rewrite the predicate into an equivalent form to take advantage of
    indices.

3- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  Predicate "TEST1"."STATUS"7 used at line ID 1 of the execution plan is an
  inequality condition on indexed column "STATUS". This inequality condition
  prevents the optimizer from selecting indices  on table "MWARE"."TEST1".

  Recommendation
  --------------
  - Rewrite the predicate into an equivalent form to take advantage of
    indices.

4- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  Predicate "TEST1"."STATUS"8 used at line ID 1 of the execution plan is an
  inequality condition on indexed column "STATUS". This inequality condition
  prevents the optimizer from selecting indices  on table "MWARE"."TEST1".

  Recommendation
  --------------
  - Rewrite the predicate into an equivalent form to take advantage of
    indices.

5- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  Predicate "TEST1"."STATUS"9 used at line ID 1 of the execution plan is an
  inequality condition on indexed column "STATUS". This inequality condition
  prevents the optimizer from selecting indices  on table "MWARE"."TEST1".

  Recommendation
  --------------
  - Rewrite the predicate into an equivalent form to take advantage of
    indices.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 648532652


----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IXTEST1 |     1 |    13 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / TEST1@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STATUS"5 AND "STATUS"6 AND "STATUS"7 AND 
              "STATUS"8 AND "STATUS"9)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "STATUS"[NUMBER,22]

-------------------------------------------------------------------------------