What is the Difference between IN and Not IN performance wise? I am new to database.I am having doubt that what is the difference between IN and NOT IN performance wise.

When I am using IN, takes less time for the same query(Logically same query) when I use NOT IN.
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]

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