TSQL - Faster to use IN (list) or use NOT IN (list) for a small list?

Question

One of my columns can only contain 4 possible values (val1, val2, val3, val4).

I need to add an additional filter in the WHERE clause to exlude one of those 4 values (i.e. val4).

Is it going to be faster to use NOT IN ('val4') or IN ('val1','val2','val3')?

Cᴏʀʏ · Accepted Answer · 2013-07-11T22:12:11.997

4

NOT IN ('val4') is basically equivalent to

WHERE
    NOT (column = 'val4') /* or column <> 'val4' */

whereas IN ('val1', 'val2', 'val3') is basically equivalent to

WHERE
(
   column = 'val1'
   OR
   column = 'val2'
   OR 
   column = 'val3'
)

I would hope that the first option is faster, if only negligibly. The SQL engine might optimize IN differently depending on how many values you specify in the IN function and what indexes are available on your table. What behavior are you seeing that you're questioning the method to use?

edited Jul 11 '13 at 22:12

answered Jul 11 '13 at 22:05

Cᴏʀʏ

105,112
20
162
194

I would have normally just used a `!=`, but thought I had read around (on SO) that it's not always the best option and can cause performance issues. – johntrepreneur Jul 11 '13 at 22:13
what you said makes sense though. Just looking for any additional insight I might be missing. – johntrepreneur Jul 11 '13 at 22:14

paparazzo · Answer 2 · 2013-07-11T22:17:31.680

1

Check the query plan. If Val4 is non clustered index I bet both plans are an index seek.
I tested in, not in, =, and <> and all were the same query plan.
If they have the same query plan I doubt the actual performance difference would be big enough to see. But I would still go with the single value.

Since there are only a few values an indexed view would probably have the best performance.

edited Jul 11 '13 at 22:17

answered Jul 11 '13 at 22:12

paparazzo

44,497
23
105
176

TSQL - Faster to use IN (list) or use NOT IN (list) for a small list?

2 Answers2