Which is more faster - IN or EXISTS?

EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.
  • Interview Candidate
  • 26
  • 16252

Showing Answers 1 - 26 of 26 Answers

rajesh

  • Sep 14th, 2005
 

it is always better to use exists when u go correlated sub queries in for ordinary sub queries

sayeed321in

  • Apr 8th, 2007
 

Hi All,
  I think it is IN clause will give better performence than EXISTS clause,
because EXISTS clause will be executed using Top to bottom approach and IN clause will do a bottom to top approach.

suppose table1 has 100000 records and table2 has 300000 records.if we use the following query then the performence is slow:

   select * from table1 t1
    where EXISTS(select 1 from table2 t2
                            where t1.col1 = t2.col1)


          This means ,for every record in table1 it will check the table2 for 300000 records.

IN clause: if we use IN clause, the co related sub query is executed first and then the main query.
       select * from table1
        where col1 IN(select col1 from table2);

Guys please correct me if i am wrong.....

Thanx & Regards,
Ahmed Sayeed.

amit

  • Jun 29th, 2007
 

Exist is more faster than IN because IN doesn't use indexes at the time of fetching but Exist uses Index at the time of fetching.

  Was this answer useful?  Yes

Nliga

  • Jul 13th, 2011
 

I think it depends on the type of your query and how you use it.

  Was this answer useful?  Yes

yasodha

  • Aug 29th, 2011
 

I'm thinking that 'IN' is faster than "Exist' because exist want to give Boolean values for every values. But it is not in the case of "IN"

  Was this answer useful?  Yes

Vineet Arya

  • Sep 17th, 2011
 

Which is more faster - IN or EXISTS?

It totally depends upon the query. For some queries Exist is efficient and for some IN is efficient

Exits is correlated sub query, in which the outer query run first and and for each outer query inner query is probed.
where as in IN The sub query is evaluated, distinct, indexed and then joined to the original table.

So consider a one BIG table( say 1 million row) and one Small table containing relative less number of rows.

So if the outer table is small then it will probed less number of times and with the inner query(Big table) so in this case Exist will be faster.

If inner query table(Small table) is giving smaller result set than IN will be faster.

EXISTS will find the first row faster in general then the IN will and the IN will get the LAST row (all rows) faster then the where exists

  Was this answer useful?  Yes

rohitosu

  • Mar 9th, 2012
 

As a rule of thumb IN should be used when the sub query will return small result set. It should be kept in mind that the sub query should not return null values. EXISTS should be used when the result set returns large result set. Other factors that might affect query performance is the indexes

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions