Search code examples
sqlt-sqlsqlperformancenotinsql-in

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

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')?

Solution

  • 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?