SQL: BETWEEN and IN (which is faster)

Question

Possible Duplicate:
Is there a performance difference between BETWEEN and IN with MySQL or in SQL in general?

If I have the following ids:

1,2,3,4,5,6

Is it better to use IN or the between clause for deleting?

Answer 1

IN is equivalent to 1 or 2 or 3 or 4 or 5

Between is equivalent to >= 1 and <= 6

Personally I would use between in ranges, but specific circumstances and DB engines can make a difference too.

Answer 2

Depends on whether indexes are implemented. if the id is the primary key then both the queries should have the same cost. Evaluate the SQL using a profiler.

Answer 3

Between Clause, unless you expect the Id's to be different in the future.

Answer 4

• If your ids are always consecutive you should use BETWEEN.
• If your ids may or may not be consecutive then use IN.

Performance shouldn't really be the deciding factor here. Having said that, BETWEEN seems to be faster in all examples that I have tested. For example:

Without indexes, checking a table with a million rows where every row has x = 1:

SELECT COUNT(*) FROM table1 WHERE x IN (1, 2, 3, 4, 5, 6);
Time taken: 0.55s

SELECT COUNT(*) FROM table1 WHERE x BETWEEN 1 AND 6;
Time taken: 0.54s

Without indexes, checking a table with a million rows where x has unique values:

SELECT COUNT(*) FROM table1 WHERE x IN (1, 2, 3, 4, 5, 6);
Time taken: 0.65s

SELECT COUNT(*) FROM table1 WHERE x BETWEEN 1 AND 6;
Time taken: 0.36s

A more realistic example though is that the id column is unique and indexed. When you do this the performance of both queries becomes close to instant.

SELECT COUNT(*) FROM table2 WHERE x IN (1, 2, 3, 4, 5, 6);
Time taken: 0.00s

SELECT COUNT(*) FROM table2 WHERE x BETWEEN 1 AND 6;
Time taken: 0.00s

So I'd say concentrate on writing a clear SQL statement rather than worrying about minor differences in execution speed. And make sure that the table is correctly indexed because that will make the biggest difference.

Note: Tests were performed on SQL Server Express 2008 R2. Results may be different on other systems.

Answer 5

Between is faster due to lesser comparisons. With IN clause each elements are traversed every time.

But purpose of both are different:

• Between is used when you are comparing with Range of values in some kind of sequence.

• IN is used when comparing with values not in sequence.