SQL Server Performance Forum – Threads Archive

OR vs IN

Hi All , I have a doubt . I have a Where Clause where I am using OR
WHERE a.txtMode = ‘X’
OR a.txtMode = ‘Y’ And I have another query using Where clause as
WHERE a.txtMode IN (‘X’,’Y’) Which one would run faster . (And Why). the table a has some 0.8 million rows Thanx in Advance

---

Hi, I believe both are the same. When your SQL statement is normalized/parsed, the use of "IN" is translated as if you’re using the "OR". Just a tip, if you’re going to use "OR" clause, make sure that the column in the WHERE clause has a useful index. With "IN", arrange the list of values in such a way that the most frequently found values are at the beginning of the list, because the IN option returns true as soon as any of the values in the list produce a match.
Jon M

---

Did you see both executions plan? Luis Martin
Moderator
SQL-Server-Performance.com

---

In most cases when I’ve used an IN statement then looked at the execution plan, SQL converts this to an OR. IN when variables are listed (rather than a select subquery) is really just a shortcut to writing field1 = x or field1 = y or etc. Chris

---

either of the above should be ok,
however, when the or clause is on different columns, i have seen bad plans,
in which case, two separate statements with a union might be better

---

]]>