SQL Server Performance Forum – Threads Archive

EXISTS or IN?

Hello,
I need to know why the EXISTS clause is more efficient than IN clause. Can someone explain me? Thanks.

---

Both can serve a different purpose and not always is EXISTS more efficient than IN. What exactly is your question/problem? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

I thin he is refering to When you have a choice of using the IN or the EXISTS clause in your Transact-SQL, you will generally want to use the EXISTS clause, as it is more efficient and performs faster
of http://www.sql-server-performance.com/best_sql_server_performance_tips.asp —————————————-

---

EXISTS will probably be faster if the target column is relatively low in selectivity (many rows with identical values), and is not covered by an index. Otherwise an IN clause with a subquery will probably be as fast (or as slow). An IN clause with a value list is of course a different matter.

---

This actually traslated into multiple ORs Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

---

Also IN will get tricky when negated and NULLs comes into play. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

Frank, the same can be said about NOT EXISTS, is it not?

---

NO Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

---

Check out this explanation:http://snipurl.com/lnd2 —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

Sorry if I was a bit vague. Wanted to say something along the lines of : The same as with NOT IN, with NOT EXISTS performance will be more difficult to predict than with EXISTS or IN. Good to know about the issue with NULLs for a NOT IN subquery! But I must say I usually select the EXISTS or NOT EXISTS syntax.

---

People, Thank you for response. Now, I have clearly the concepts.

---

Aha.. really good article .. Chirag

---

]]>