EXISTS vs IN - Which is the fastest one

Posted by Sivakumar.S under Sql Server on 7/10/2012 | Points: 10 | Views : 1044 | Status : [Member] | Replies : 7
Hi,

In sql server if we go for performance perspective, which is the fastest one whether EXISTS or IN ?

siva


Responses

Posted by: Sumit.Bundiwal on: 7/10/2012 [Member] Starter | Points: 25

Up
0
Down
Exist is fast because is only return true or false

sumit bundiwal

Sivakumar.S, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Naraayanan on: 7/10/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
Check this link
http://bytes.com/topic/sql-server/answers/83062-more-efficient-exists

Regards,
Lakshmi Naraayanan.S
http://dotnettechrocks.blogspot.in/
http://abaprocker.blogspot.com/

Sivakumar.S, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sivakumar.S on: 7/10/2012 [Member] Starter | Points: 25

Up
0
Down
Hi all,

Need explanation in depth with examples.

Thanks in advance


siva

Sivakumar.S, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rickeybglr on: 7/11/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Sivakumar,
Exist is much faster than In because:
1. exist checks boolean condition either true or false where as IN checks multiple string (used to find any matches of value in column) --which take much time
2. IN does not use any index where as Exist use indexes

Sivakumar.S, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patel28rajendra on: 7/11/2012 [Member] Starter | Points: 25

Up
0
Down
Hi

it depends on your Query and Sub Query and its result


R D Patel

Sivakumar.S, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patel28rajendra on: 7/11/2012 [Member] Starter | Points: 25

Up
0
Down
Hi

visit this link for more information

http://stackoverflow.com/questions/24929/difference-between-exists-and-in-in-sql

R D Patel

Sivakumar.S, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/4/2012 [Member] Bronze | Points: 25

Up
0
Down
EXISTS will be faster because once the engine has found a hit, it will quit looking as the condition has proved true. With IN it will collect all the results from the subquery before further processing.

Sivakumar.S, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response