SQL - IN vs. NOT IN

Question

Suppose I have a table with a column that takes values ​​from 1 to 10. I need to select columns with all values ​​except 9 and 10. Will there be a difference (in performance) when using 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

When it comes to performance, you should always profile your code (i.e., run your queries a few thousand times and measure the performance of each loop with a stopwatch Example ).

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

Edit: I remember that this was marked as php, so I provided the sample in php, but I could be wrong. I think it will be difficult to rewrite this sample in the language you use.

Answer 2

Use "IN", as this will most likely force the DBMS to use the index in the corresponding column.

"NOT IN" could theoretically also be translated into an index, but in a more complex way that a DBMS cannot "spend overhead" with.

Answer 3

I have seen that Oracle cannot optimize some queries with NOT IN if the columns are NULL. If you can write your query anyway, IN is preferred as far as I know.

Answer 4

For a list of constants, MySQL will internally extend your code to:

 SELECT * FROM tbl WHERE ((col <> 9 and col <> 10)) 

Same thing for another, instead of 8 times = .

So yes, the first will be faster, fewer comparisons. The chances of it being measurable are negligible, but the overhead of a few ongoing comparisons is no different from the general overhead of parsing SQL and getting data.