Perfomance Question - IN vs OR
-
Which method would provide the best query performance?
-
INNER JOIN
_____________
Code for TallyGenerator -
If a simple list then there is no difference as the query engine translates treates both as OR. The major gain is that IN is much easier to read than a bunch of OR and less likely to make a mistake, especially if you have a number of ANDs.
WHERE B = 'A' AND A IN (1,2,3,4) AND C = 8
Does not equal
WHERE B = 'A' AND A = 1 OR A = 2 OR A = 3 OR A = 4 AND C = 8
But is equal to
WHERE B = 'A' AND ( A = 1 OR A = 2 OR A = 3 OR A = 4 ) AND C = 8
You will more likely find yourself trobuleshooting the difference between Example 3 and 2 by accidentally using 3.
If however is IN a select of data from another table then many time INNER JOIN is better but I don't think that is where you were going.
-
I would say that both previous answers are correct
IN is easier to maintain than OR and performs well with not-too-high number of values, but if you need best performance and the IN list can be more than a few values long, consider inserting the values into a temporary table or table variable and use JOIN instead.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply