SQL Server Performance Forum – Threads Archive

EXISTS vs IN

Hi,
Could sombody explain which leads to better performance and why, using IN or Exists.
using IN:
select col1, col2, col3 from table 1
where col4 IN (select col1 from table 2
where col2 IN (select col1 from table 3 where col1=50))
Using Exists:
select col1, col2, col3 from table 1
where col4 Exists (select col1 from table 2
where col2 Exists (select col1 from table 3 where col1=50))
Thanks.

---

using EXISTS is always better then IN Keyword.
But ur query doesnt seems to be better

---

It’s not clear whether you’re talking about a single table or about three separate tables. Also, your EXISTS clause is not correct: check BOL for the syntax. Typically, nested subqueries degrade performance. You may be better off using joins (or self-joins, in case it’s a single table) – plus DISTINCT or GROUP BY.

---

EXISTS() is not always better than another solution. Many times you can flatten this to a join which might run faster. A lot of factors affect the performance. The only reliable answer here is to build up a test scenario and run each alternative against a considerable amount of your own data. —
Frank
http://www.insidesql.de

---

Looking at your example Exists leads better performance, since it only checks for true or false condition. In your first select stmt, the outer select has to compare each value (col4 with col1) and then get the results back. So based on the query, we have to think which one to use. Both has proc and cons (based on table size and with final results set). srinivas

---

]]>