bhanu

• May 11th, 2006

 

Exists is preferred and it's more faster than IN clause....

IN check for all the values and where as EXISTS looks for values one by one and it exists when it found the value....

David

• May 16th, 2006

 

i wld like to answer that question.

in subqueries the inner clause is important coz its results acts like the input of the other outer query.

So its important that the inner clause should be right.

(i m sure that my ansewer is right,but if better answer u can help me out!!.

coz i m doing BSc-IT.....)

Lokesh Bhat

• May 23rd, 2006

 

 

EXISTS is efficient bcose,

1.Exists is faster than IN clause.

2.IN check returns values to main query where as EXISTS returns Boolean (T or F).

JayGohil

 Profile Answers by JayGohil 

• May 5th, 2007

 

I have confusion here. As per answer of previous question about difference Between IN and EXISTS, inner query is executed only once for IN predicate and multiple times for EXISTS predicate ( once for every raw of outer query result). So, I guess IN is efficient than EXISTS, isn't it? Think about multiple rows retrived by inner and outer query.

Thanks.

Arpana

• Oct 10th, 2007

 

EXISTS is more efficient that IN , Because EXISTS use indexes of the table and hence scans the table faster, as well as it returns the boolean value (T or F), If T is received for EXISTS clause than the rows will be returned otherwise not.
where as IN works as simple query where it will scan all possible values in the table and than compares the condition given by you and then the result.

PrabhatKumar

 Profile Answers by PrabhatKumar 

• Oct 7th, 2010

 

The EXISTS function searches for the presence of a single row meeting the stated criteria as opposed to the IN statement which looks for all occurrences.
 Rule of thumb:

• If the majority of the filtering criteria are in the subquery then the IN variation may be more performant.
• If the majority of the filtering criteria are in the top query then the EXISTS variation may be more performant.