Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Logically, IN and NOT IN are completely different. Perfomance-wise, they can be too. –  Colin 't Hart Feb 5 at 9:20
    
Ok.I am having tow queries one with IN and Other With NOT IN,both return the same record but the time taken by query containing NOT IN is more. –  nitesh.kodle123 Feb 5 at 9:36
3  
Please update your question to include your example. –  Colin 't Hart Feb 5 at 10:09
    
And which DBMS are you using? –  Colin 't Hart Feb 5 at 10:48
1  
Well, expresion IN (1,2,7) and expression NOT IN (3,4,5,6,8,9,10) are equivalent if the expression/column is only allowed to get these 10 values. Is your example similar? –  ypercube Feb 5 at 21:57

1 Answer 1

up vote 1 down vote accepted

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 <= 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]

-------------------------------------------------------------------------------
share|improve this answer
    
Oracle can use an index for NOT IN too. –  Colin 't Hart Feb 15 at 18:11
    
I didn't say it can't use an index, it can make better use of it. –  Dieter DHoker Feb 15 at 18:56
    
I'm curious, does anything change if you add a constraint CHECK (status IN (0,1,2,3,4,5,6,7,8,9))? –  ypercube Feb 15 at 19:30
    
no eg. if you add the check constraint and do: select * from TEST1 where status in (0); select * from TEST1 where status not in (1,2,3,4,5,6,7,8,9); the first will have lower cost execution plan. –  Dieter DHoker Feb 15 at 20:47
    
And what about comparing WHERE status IN (0,1,2,3,4,5,7,8,9) with WHERE status NOT IN (6)? –  ypercube Feb 15 at 20:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.