krishna sai

• Jun 12th, 2007

 

exist is faster than IN Command

exist do full table scan...so it is faster than IN

Ranjith A Ramachandran

• Jul 25th, 2007

 

Use Exists whenever possible. EXISTS only checks the existence of records (True/False), and in the case of IN each and every records will be checked.  performace wise EXISTS is better.

Donram

• Nov 26th, 2007

 

If your inner query returns a few rows to the outer query, use IN otherwise use EXISTS .

In -- Full table Scan
Exists -- Existance of rows

mohit_aren

 Profile Answers by mohit_aren 

• Feb 12th, 2008

 

IN is used in normal SELECT queries:

e.g.
SELECT * FROM EMP WHERE EMPID IN (12,13,15)

We cannot use EXIST to fetch the rows with 12,13,15 EMP IDs

EXIST only tells if a SELECT query will fetch any row - true, no rows - false

e.g.
IF EXISTS(SELECT * FROM EMP WHERE EMPID IN (12,13,15))
     DELETE FROM EMP WHERE EMPID IN (12,13,15)
this will fire the DELETE statement only once as next time there will be no rows with these EMPIDs

thus there is basic difference in both

jryan999

 Profile Answers by jryan999 Questions by jryan999

• Apr 30th, 2008

 

Firstly, in common with Tom Kyte, I could scream hearing comments that IN is "better" or faster than exists.  Nonsense.

There are situations where IN is much faster (and vice versa).

eg.  select  *
       from    MASSIVE_TABLE T
       where T.indexed_field in (select result
                                                from   VERY_SMALL_RESULT_SET)

Using IN, if resulting in the index on MASSIVE_TABLE being used be MUCH faster than an exists.

For a full comparison see Tom Kytes explaination at:-
http://asktom.oracle.com/pls/asktom/f?p=100:11:2957834641539512::::P11_QUESTION_ID:953229842074

From memory, here are some differences:-

1.  IN a subquery - must retrieve the entire query set before evaluating the outer query

2.  EXISTS on the other hand will stop executing a sub-query when the first entry is found.  This may in some cases favour EXISTS over IN

3.  "Where EXISTS (select null from..." clearly cannot use any index on outer query table whereas IN can.  (See explaination above)

In short, there are cases where IN is better than EXISTS (it can mean an index on the outer query may be used).  It depends upon the relative size of the "inner" and "outer" tables, and indexes in place.

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 3rd, 2008

 

IN or EXISTS which gives better performance depends upon the situation.

 

While using IN , optimizer runs subquery first and then joins it with main dataset. Table in the subqery is small and table in the main query is large then IN usaully makes sense.

 

Using EXISTS, optimizer runs main query first and then applies dataset to the subquery. If the main table size is small and subquery table size is large then EXISTS usually makes sense.

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 8th, 2008

 

IN or EXISTS which gives better performance depends upon the situation.

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select * 
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to
the original table -- typically.

While using IN , optimizer runs subquery first and then joins it with main dataset. Table in the subqery is small and table in the main query is large then IN usaully makes sense.

select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:


   for x in ( select * from t1 )
   loop
      if ( exists ( select null from t2 where y = x.x )
      then
         OUTPUT THE RECORD
      end if
   end loop

It always results in a full scan of T1 
 

Using EXISTS, optimizer runs main query first and then applies dataset to the subquery. If the main table size is small and subquery table size is large then EXISTS usually makes sense.

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 8th, 2008

 

IN or EXISTS which gives better performance depends upon the situation.Select * from T1 where x in ( select y from T2 )is typically processed as:select * from t1, ( select distinct y from t2 ) t2 where t1.x = t2.y;The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table -- typically.While using IN , optimizer runs subquery first and then joins it with main dataset. Table in the subqery is small and table in the main query is large then IN usaully makes sense.select * from t1 where exists ( select null from t2 where y = x )That is processed more like: for x in ( select * from t1 ) loop if ( exists ( select null from t2 where y = x.x ) then OUTPUT THE RECORD end if end loopIt always results in a full scan of T1 Using EXISTS, optimizer runs main query first and then applies dataset to the subquery. If the main table size is small and subquery table size is large then EXISTS usually makes sense.

be_viral

 Profile Answers by be_viral Questions by be_viral

• Jul 14th, 2008

 

We should use Exists or In based on the no of records inner and outer query is expected to return.Using Exists : Here the outer query is executed first and then inner query is excecuted for each value of outer query. So use this when outer query contains less records.Using IN : Here the inner query is executed first and then outer query is excecuted for each value of inner query. So use this when inner query contains less records.

saeed

• Jul 25th, 2011

 

Compare EXISTS and IN Usage with advantages and disadvantages.