5

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?

Community
  • 1
  • 1
RPS
  • 807
  • 2
  • 13
  • 20
  • I would think that the performance is the same for querying or deleting. – FrustratedWithFormsDesigner Jul 29 '10 at 15:40
  • 2
    Honestly, this sort of question cannot be answered beyond *it depends*. `between` seems right here, but is it faster? Will it always be faster? It is considerable, in context of deleting data? What DBMS do you have, indices, etc. Only you can answer that, by checking. – Kobi Jul 29 '10 at 15:40

5 Answers5

11
  • 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.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • I have surprises, with one of `in`/`between` using the indexes, and not the other. In both ways. Making a query suddenly misbehave (depending on actual values) because it is processed as a `full scan` instead of the *logical* `index scan`... – pascal Jul 29 '10 at 19:00
  • In most cases I agree with Mark, but note that when performance IS a factor you should consider your indexes and benchmark both options. I have had cases in MySQL where using `IN` was much faster than using `BETWEEN`. – Code Commander Oct 06 '12 at 17:27
4

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.

Jeremy
  • 4,808
  • 2
  • 21
  • 24
  • 2
    +1, **I never use between**, does it include endpoints? everyone seems to forget, so I use `>=` or `>` along with `<=` or `<` and it is crystal clear if endpoints are included or not. – KM. Jul 29 '10 at 15:49
  • @KM, it does include endpoints, between is inclusive, and imho, misnamed. – CaffGeek Jul 29 '10 at 16:05
  • @Chad, thanks ;-) but I said `everyone seems to forget`, not that I forgot. To me in English, `between` means the items inside. so when I say you can have everything between my hands, I don't mean my hands too! by writing the code to include the actual `>=`/`>` and `<=`/`<` it becomes clear to exactly what you intend, which goes to an easier understanding of the code. – KM. Jul 29 '10 at 18:10
  • @KM @CaffGeek, while <= and >= are more descriptive, they require you to duplicate the operand which increases the burden of code maintenance and IMO makes the query harder to read. I think is is reasonable to expect that people know that `BETWEEN` is inclusive, but if you don't I might recommend using `BETWEEN` with an inline comment `-- INCLUSIVE` rather than repeating your operand. – Code Commander Oct 06 '12 at 17:04
  • @Code Commander, the longer you work with SQL, the more you find you have to duplicate blocks of code, and the less you'll still care about it. – KM. Oct 08 '12 at 17:55
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.

TrustyCoder
  • 4,749
  • 10
  • 66
  • 119
1

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

Viv
  • 2,515
  • 2
  • 22
  • 26
0

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.

YoK
  • 14,329
  • 4
  • 49
  • 67