SQL - IN vs. NOT IN

Question

Suppose I have a table with column which takes values from 1 to 10. I need to select columns with all values except for 9 and 10. Will there be a difference (performance-wise) when I use this query:

SELECT * FROM tbl WHERE col NOT IN (9, 10)

and this one?

SELECT * FROM tbl WHERE col IN (1, 2, 3, 4, 5, 6, 7, 8)

Answer 1

Use "IN" as it will most likely make the DBMS use an index on the corresponding column.

"NOT IN" could in theory also be translated into an index usage, but in a more complicated way which DBMS might not "spend overhead time" using.

Answer 2

When it comes to performance you should always profile your code (i.e. run your queries few thousand times and measure each loops performance using some kind of stopwatch. Sample).

But here I highly recommend using the first query for better future maintaining. The logic is that you need all records but 9 and 10. If you add value 11 to your table and use second query, logic of your application will be broken that will lead to bug, of course.

Edit: I remember this was tagged as php that's why I provided sample in php, but I might be mistaken. I guess it won't be hard to rewrite that sample in the language you're using.